Programming a chat bot

  • lzook

    (@lzook)


    8 years, 7 months ago

    Hey everyone. Okay I have gotten myself to a point and then need help past that. I am working on a website that has woocommerce and giga.ai chat bot for facebook installed.

    I am working on an add action for giga.ai to be able to look up the order status of a customer’s order. However, I have sequential order numbers for woocommerce installed.

    so for this to work, you would need to look up the customers order number that they get from the site to get the “post id” which is the true order number. Then use that order number to look up the order status.

    
add_action('giga_pre_seed', function ($bot) {

    $bot->answers('@order', function ($bot, $lead_id, $input) {

                // You use $wpdb object so you'll need to use global variable

        global $wpdb;
		          
        $order_number_id = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE meta_value=%s", $input));

	$order_status_id = $wpdb->get_var($wpdb->prepare("SELECT post_status from $wpdb->post WHERE ID=%d", $order_number_id));
			
		$bot->says($order_status_id);		
          
    }); 

});

    so the code works to get the first part (order_number_id) with out issues. it returns the proper post_id. The issue comes just below that when I try to use the order_number_id to retrieve the order status from the wp_post table.

    any help you can offer will be greatly appreciated.

Viewing 3 replies - 1 through 3 (of 3 total)
  • anonymized-14293447

    (@anonymized-14293447)

    8 years, 7 months ago

    Hello, probably not the answer you want, but earlier today I bumped into a genius plugin that helps you set all sorts of bots: search for GOBOT in the repository 🙂

    Thread Starter lzook

    (@lzook)

    8 years, 7 months ago

    So I actually was able to get this to work great. I forgot and blanked on just doing an inner join between the two tables instead of trying to pass variables around.

    The Working Code:

    
add_action('giga_pre_seed', function ($bot) {

    $bot->answers('@order', function ($bot, $lead_id, $input) {

                // You use $wpdb object so you'll need to use global variable

        global $wpdb;       
		
		$order_status = $wpdb->get_var($wpdb->prepare("SELECT wp_posts.post_status 
														FROM wp_postmeta
														INNER JOIN wp_posts
														ON wp_postmeta.post_id = wp_posts.ID
														WHERE wp_postmeta.meta_value=%s", $input));
		
		$bot->says($order_status);		
          
    }); 

});
    Dion

    (@diondesigns)

    8 years, 7 months ago

    It actually doesn’t work great. If your postmeta table is even reasonably large, that query is going to cause problems for your site. The reason is that the meta_value column isn’t indexed, so queries are going to be slow and CPU-intensive. You can minimize the damage by limiting the query to the specific meta_key, but that too will be slow if you have more than a few hundred posts.

    The best solution is to have this data in its own properly-indexed DB table. Based on what I’ve seen recently in WP plugins, however, there is a real possibility that the plugin author doesn’t know what a database schema is…

    (Hmm. Another solution is to have a unique index on the meta_key/meta_value pair in both the postmeta and usermeta tables. The best solution is to throw away the 2005-era schema of those tables and instead have the meta keya as columns.)

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Programming a chat bot’ is closed to new replies.