sql - How can I combine one field using wpdb and group by?

admin管理员组

文章数量:1122832

I have the following code in my functions.php where I'm trying to group the quantity sold from woocommerce orders into the variation selected on the product. My query is:

$uvuorderposts = $wpdb->get_results( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value, wcomm2.meta_key AS meta_key_qty, wcomm2.meta_value AS meta_value_qty, pm2.meta_value AS meta_value_location
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm2 ON wcom.order_item_id = wcomm2.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm3 ON wcom.order_item_id = wcomm3.order_item_id
    INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = wcomm3.meta_value
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    AND (wcomm.meta_value = '4034')
    AND (wcomm2.meta_key = '_qty')
    AND (wcomm3.meta_key = '_variation_id')
    AND (pm2.meta_key = 'attribute_pa_seminar-location')
    ORDER BY p.post_date
    "
);

echo "<table>";
foreach ( $uvuorderposts as $uvuorderpost ) 
{
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . $uvuorderpost->meta_key_qty . " : " .  $uvuorderpost->meta_value_qty . "</td></tr>";
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . strtoupper($uvuorderpost->meta_value_location) . "</td></tr>";      
}
echo "</table>";

The output gives me:

_qty : 1
WY
_qty : 1
WV
_qty : 1
GA
_qty : 1
GA

I need it to actually be:

_qty : 1
WY
_qty : 1
WV
_qty : 2
GA

I added GROUP BY pm2.meta_value and it took away the second GA one, but did not combine the _qty value. Please help with how to combine the _qty value between returned records?

I have the following code in my functions.php where I'm trying to group the quantity sold from woocommerce orders into the variation selected on the product. My query is:

$uvuorderposts = $wpdb->get_results( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value, wcomm2.meta_key AS meta_key_qty, wcomm2.meta_value AS meta_value_qty, pm2.meta_value AS meta_value_location
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm2 ON wcom.order_item_id = wcomm2.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm3 ON wcom.order_item_id = wcomm3.order_item_id
    INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = wcomm3.meta_value
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    AND (wcomm.meta_value = '4034')
    AND (wcomm2.meta_key = '_qty')
    AND (wcomm3.meta_key = '_variation_id')
    AND (pm2.meta_key = 'attribute_pa_seminar-location')
    ORDER BY p.post_date
    "
);

echo "<table>";
foreach ( $uvuorderposts as $uvuorderpost ) 
{
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . $uvuorderpost->meta_key_qty . " : " .  $uvuorderpost->meta_value_qty . "</td></tr>";
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . strtoupper($uvuorderpost->meta_value_location) . "</td></tr>";      
}
echo "</table>";

The output gives me:

_qty : 1
WY
_qty : 1
WV
_qty : 1
GA
_qty : 1
GA

I need it to actually be:

_qty : 1
WY
_qty : 1
WV
_qty : 2
GA

I added GROUP BY pm2.meta_value and it took away the second GA one, but did not combine the _qty value. Please help with how to combine the _qty value between returned records?

• wpdb
• sql

Share Improve this question asked Oct 12, 2015 at 19:38 user81828user81828 1122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Got it working with

$uvusql = $wpdb->prepare( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value, wcomm2.meta_key AS meta_key_qty, wcomm2.meta_value AS meta_value_qty, pm2.meta_value AS meta_value_location
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm2 ON wcom.order_item_id = wcomm2.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm3 ON wcom.order_item_id = wcomm3.order_item_id
    INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = wcomm3.meta_value
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    AND (wcomm.meta_value = '4034')
    AND (wcomm2.meta_key = '_qty')
    AND (wcomm3.meta_key = '_variation_id')
    AND (pm2.meta_key = 'attribute_pa_seminar-location')
    ORDER BY p.post_date
    "
);

$uvuorderposts = $wpdb->get_results( $uvusql, ARRAY_A );

 $uvugroups = array();
    $uvukey = "";
    $uvukeyc = 0;
    foreach ($uvuorderposts as $item) {
        $uvukey = $item['meta_value_location'];
        if (!array_key_exists($uvukey, $uvugroups)) {
            $uvugroups[$uvukey] = array(
                'id' => $item['meta_value_location'],
                'qty' => $item['meta_value_qty'],
            );
        } else {
            $uvugroups[$uvukey]['qty'] = $uvugroups[$uvukey]['qty'] + $item['meta_value_qty'];
        }
        $uvukeyc++;                       
    }

    print "<pre>";
    print_r($uvugroups);
    print "</pre>";

本文标签： sqlHow can I combine one field using wpdb and group by