Query Return a list of product categories and orders …

  • birdietorerik

    (@birdietorerik)


    8 years, 6 months ago

    Hi!

    Have this query, that display order information from woocommerce

    
select
        p.ID as order_id,
        p.post_date,
        max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
        max( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_phone,
        max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
        max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
        max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
        max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
        max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
        ( select group_concat( order_item_name separator '|' ) from wpe9_woocommerce_order_items where order_id = p.ID and wpe9_woocommerce_order_items.order_item_type='line_item') as order_items
       from
         {$wpdb->prefix}posts as p
         join {$wpdb->prefix}postmeta as pm
         join {$wpdb->prefix}term_relationships as wptr
       where
          post_type = 'shop_order' and
          p.ID = pm.post_id and
          post_date BETWEEN '2015-01-01' AND '2017-12-31'
          group by
          p.post_date desc

    But need to get all categories for products in the order using
    ( select group_concat……

    So it give me the list of categories in a string…

    How can i do this ?

Viewing 9 replies - 1 through 9 (of 9 total)
  • Moderator bcworkz

    (@bcworkz)

    8 years, 6 months ago

    To get to the category term names, you need to join in term_taxonomy and terms tables. Given the order ID (object_id) in term_relationships, you get the term_taxonomy_id, with which in term_taxonomy you can get the term ID, with which you can finally get the term name in the terms table.

    It gets pretty convoluted. Personally, I’d manage categories with PHP after I get the order ID returned. You can use wp_get_post_categories(), which returns an array of term objects by default, but you can get just the term names in an array if you prefer. Then just implode() the array to get a string list of category names.

    Thread Starter birdietorerik

    (@birdietorerik)

    8 years, 6 months ago

    Hi!

    Thank you for BIG help.

    Use this code to get categories…

    // Get post categoryes
    $post_categories = wp_get_post_categories( $row->ID );
    $cats = array();

    foreach($post_categories as $c){
    $cat = get_category( $c );
    $cats[] = array( ‘name’ => $cat->name, ‘slug’ => $cat->slug );
    error_log(“Category Name :” .$cat->name);
    }

    But get this error message in PHP log

    PHP Notice: Undefined property: stdClass::$ID in /Applications/MAMP/htdocs/wp-content/plugins/leadjab_import/includes/leadjab_api.php on line 115

    Thread Starter birdietorerik

    (@birdietorerik)

    8 years, 6 months ago

    Hi!

    Now the PHP error is gone, but no categoryes is found ????

    What can be the problem ?

    Thread Starter birdietorerik

    (@birdietorerik)

    8 years, 6 months ago

    My new code is :

    
                   $order_ID = $row->order_id;

                    error_log("ORDER_ID ER :" .$order_ID);

                    //$status = send_customers_leadjabber($json);
                    $tmpemail = $row->billing_email;
                    $number_records = $number_records + 1;
                    $resultarr[]=$row->billing_email;
                    //$order_ID = -1;
                    // Get post categoryes
                    $post_categories = wp_get_post_categories($order_ID);
                    $cats = array();
                    //error_log("POST_CATEGORY : ".$post_categories);
                    foreach($post_categories as $c){
                        $cat = get_category( $c );
                        $cats[] = array( 'name' => $cat->name, 'slug' => $cat->slug );
                        error_log("Category" .$cat->name);
                    }
    Moderator bcworkz

    (@bcworkz)

    8 years, 6 months ago

    You’re getting $order_ID as -1 ?? There’s a problem with your query then, or was that just a test? Does $order_ID in the error log show as an integer? In your foreach, the value assigned to $c is a term object by default, no need to get the category object with it. I think $cat ends up being null if you pass a non-integer to get_category(). You don’t really need the foreach structure anyway.

    With wp_get_post_categories(), you can pass an optional second argument complying with WP_Term_Query::__construct(). You could use the “fields” argument to have wp_get_post_categories() directly return the array you are currently building into $cats.

    Thread Starter birdietorerik

    (@birdietorerik)

    8 years, 6 months ago

    Hi!

    NO, i have comment out $order_id = -1 . (just testing)

    Dident figer out why i get blank result ?

    So i solved this in onother way.

    Used 2 querys, to give me what i want.

    But still wondering why wp_get_post_categories(), dosent work ?

    Tryed to use wp_get_post_categories(), with a constant value like -> wp_get_post_categories(42);
    But no category is returned ?

    Thanks for all your help

    • This reply was modified 8 years, 6 months ago by birdietorerik.
    Moderator bcworkz

    (@bcworkz)

    8 years, 6 months ago

    Nothing is returned?? Not even a WP_Error object? Something is wrong, a theme or plugin conflict of some sort. If you were to revert to the default state it would work.

    I just realized by default it returns an array of IDs, not term objects. It’s still returning something, but it explains why your foreach doesn’t work. To get objects you need to pass a “fields” argument, like so:
    wp_get_post_categories( $order_ID, ['fields'=>'all',]);

    But if you’re just after category names, use “names” as the fields argument:
    wp_get_post_categories( $order_ID, ['fields'=>'names',]);

    Thread Starter birdietorerik

    (@birdietorerik)

    8 years, 6 months ago

    Hi!

    What is $order_ID ?
    I was using post_id here ?

    Please USE my query, and explane How to USE wp_get_post…

    Moderator bcworkz

    (@bcworkz)

    8 years, 6 months ago

    $order_ID was from your “new code”, 3 of your replies back. It is beside the point really. What is needed as an argument is a post ID. It can be 42 if that is a valid post ID. It can be $anything_you_want as long as it’s a valid post ID. Usage is as shown in my last reply. The second example returns an array of category term names. It may be an array of one element or several, depending on the terms assigned to the post. If you have PHP version < 5.4, substitute the explicit array declaration for the square bracket syntax.

    If you get a WP_Error object returned, the post ID is probably invalid. Check the error object properties for specifics. If nothing at all is returned, there is a theme or plugin conflict that needs to be resolved.

    Example in place of your new code, excluding unrelated code:

    $order_ID = $row->order_id;
$cats = wp_get_post_categories( $order_ID, ['fields'=>'names',]);

    You don’t need a foreach loop to build a name array this way. This version of $cats does not include slugs, if you need slugs as well, make another call using ‘slugs’ as the fields argument.

    Full documentation: https://developer.wordpress.org/reference/functions/wp_get_post_categories/

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

The topic ‘Query Return a list of product categories and orders …’ is closed to new replies.