SQL Archives - Wordpress Developers in Bromley, London

A place to keep snippets of useful WordPress SQL.

Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

SELECT  p.ID,p.post_title,
        MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want
 FROM    wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY
   p.ID, p.post_title

SELECT p.ID,p.post_title,

MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want

FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY

p.ID, p.post_title

In the example above I’m searching for posts with a value for id_vim_node. This is based on this code from stackexchange

Output WP_Query sql code (very useful for debugging WP_Query )

if in the loop

<?php echo $GLOBALS['wp_query']->request; ?>

1	<?php echo $GLOBALS['wp_query']->request; ?>

outside of loop

$customPosts = new WP_Query($yourArgs);
echo "Last SQL-Query: {$customPosts->request}";

1 2	$customPosts = new WP_Query($yourArgs); echo "Last SQL-Query: {$customPosts->request}";

based on this post on stackexchange

This post is just a place to store handy SQL queries for Woocommerce.

Get Order and all its information

select
    p.ID as order_id,
    p.post_date,
    pm.*
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    p.ID = 14223

select

p.ID as order_id,

p.post_date,

pm.*

from

wp_posts p

join wp_postmeta pm on p.ID = pm.post_id

join wp_woocommerce_order_items oi on p.ID = oi.order_id

where

post_type = 'shop_order' and

p.ID = 14223

Get Users and billing and shipping addresses in Woocommerce

SELECT u.id, u.user_login, u.user_email, 

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END   ) as billing_email,

    max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,
    max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,
    max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,
    max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,
    max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,
    max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,
    max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,
    max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,
    max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,
    max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,
    max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,
    max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,
    max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,
    max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u
LEFT JOIN wp_usermeta m
ON  u.ID = m.user_id
group by u.ID

SELECT u.id, u.user_login, u.user_email,

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END ) as billing_email,

max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,

max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,

max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,

max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,

max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,

max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,

max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,

max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,

max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,

max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,

max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,

max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,

max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,

max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u

LEFT JOIN wp_usermeta m

ON u.ID = m.user_id

group by u.ID

this bit of sql is based on this post at http://codecharismatic.com/

Get Order Objects (not strictly sql, but seems a good place to drop it)

    global $woocommerce;						
    $args = array(  'post_type'=> 'shop_order', 
                    'post_status' => 'publish', 
                    'posts_per_page' => -1, 
                    'tax_query' => array(  
                                            array(  'taxonomy' => 'shop_order_status',
                                                    'field' => 'slug',
                                                    'terms' => array('processing')
                                                )
                                        )
                  );	
    
    $orders = array();
    

    $loop = new WP_Query( $args );						
    while ( $loop->have_posts() ) : $loop->the_post(); 

        	$order_id = $loop->post->ID;								
                $order = new WC_Order($order_id);
                
                
                echo "id order = ".$order_id;
                
                $orders[] = $order;
    
    
    endwhile;
    
    
    
    return $orders;

global $woocommerce;

$args = array( 'post_type'=> 'shop_order',

'post_status' => 'publish',

'posts_per_page' => -1,

'tax_query' => array(

array( 'taxonomy' => 'shop_order_status',

'field' => 'slug',

'terms' => array('processing')

)

);

$orders = array();

$loop = new WP_Query( $args );

while ( $loop->have_posts() ) : $loop->the_post();

$order_id = $loop->post->ID;

$order = new WC_Order($order_id);

echo "id order = ".$order_id;

$orders[] = $order;

endwhile;

return $orders;

WordPress SQL snippets

Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

Output WP_Query sql code (very useful for debugging WP_Query )

Importing a MAMP mysql database on mac (OS X Yosemite)

Handy Woocommerce SQL queries

Get Order and all its information

Get Users and billing and shipping addresses in Woocommerce

Get Order Objects (not strictly sql, but seems a good place to drop it)