Question 166590 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can a WordPress-MySQL Left Join be used if a WHERE clause does not exist?

I have the following query. It works great, except when the row for _billing_company does not exists.
Can I get some kind of a default or an empty value if _billing_company does not exist?
In the example below, the post with ID 1 will be shown, but not the one with ID 2, because there is no _billing_company column in postmeta.
I would like to get all posts, regardless of whether all of the meta_keys exist. How is that possible?

SELECT p.post_date
    , p.ID
    , m1.meta_value AS 'order_total'
    , m2.meta_value AS 'currency'
    , m3.meta_value AS '_billing_company'
    , m4.meta_value AS '_billing_first_name'
    , m5.meta_value AS '_billing_last_name'
FROM dawp_posts p
LEFT JOIN dawp_postmeta m1
    ON p.ID = m1.post_id
LEFT JOIN dawp_postmeta m2
    ON p.ID = m2.post_id
LEFT JOIN dawp_postmeta m3
    ON p.ID = m3.post_id
LEFT JOIN dawp_postmeta m4 
    ON p.ID = m4.post_id
LEFT JOIN dawp_postmeta m5
    ON p.ID = m5.post_id
WHERE p.post_type = 'shop_order' 
    AND p.post_status NOT LIKE 'wc-completed' 
    AND m1.meta_key = '_order_total'
    AND m2.meta_key = '_order_currency'
    AND m3.meta_key = '_billing_company'
    AND m4.meta_key = '_billing_first_name'
    AND m5.meta_key = '_billing_last_name'
ORDER BY p.post_date DESC

Tables:
dawp_posts

ID post_name post_date post_status …
1 Hello 2020-03-03 open …
2 World 2021-01-01 open …

dawp_postmeta

post_id meta_key meta_value
1 _order_currency CHF
1 _billing_first_name Daniel
1 _order_total 10
1 _billing_last_name Boxero
1 _billing_company Nonero Gmbh
2 _order_currency CHF
2 _billing_first_name Markus
2 _order_total 50
2 _billing_last_name Nachinzki
Tags:

2

Answers


  1. 0

    When using LEFT JOIN, conditions on all but the first table should be in the ON clauses:

    FROM `dawp_posts` `posts` LEFT JOIN
     `dawp_postmeta` `m1`
     ON `posts`.`ID` = `m1`.`post_id` AND `m1`.`meta_key` = '_order_total' LEFT JOIN
     `dawp_postmeta` `m2`
     ON `posts`.`ID` = `m2`.`post_id` AND `m2`.`meta_key` = '_order_currency' LEFT JOIN
     `dawp_postmeta` `m3`
     ON `posts`.`ID` = `m3`.`post_id` AND `m3`.`meta_key` = '_billing_company' LEFT JOIN
     `dawp_postmeta` `m4`
     ON `posts`.`ID` = `m4`.`post_id` AND `m4`.`meta_key` = '_billing_first_name' LEFT JOIN
     `dawp_postmeta` `m5`
     ON `posts`.`ID` = `m5`.`post_id` AND `m5`.`meta_key` = '_billing_last_name'
WHERE `posts`.`post_type` = 'shop_order' AND
      `posts`.`post_status` NOT LIKE 'wc-completed' ;

    I would also suggest using more meaningfull table aliases such as mot, moc, mbc — that is abbreviations for what the tables mean.

    Login or Signup to reply.
  2. 0

    FWIW, while it does nothing for performance, I find this easier to read…

    SELECT p.post_date
     , p.ID
     , MAX(CASE WHEN m.meta_key = '_order_total'        THEN meta_value END) order_total
     , MAX(CASE WHEN m.meta_key = '_order_currency'     THEN meta_value END) currency
     , MAX(CASE WHEN m.meta_key = '_billing_company'    THEN meta_value END) _billing_company
     , MAX(CASE WHEN m.meta_key = '_billing_first_name' THEN meta_value END) _billing_first_name
     , MAX(CASE WHEN m.meta_key = '_billing_last_name'  THEN meta_value END) _billing_last_name
  FROM dawp_posts p
  LEFT
  JOIN dawp_postmeta m
    ON m.post_id = p.ID
 WHERE p.post_type = 'shop_order' 
   AND p.post_status NOT LIKE 'wc-completed' 
 GROUP 
    BY p.post_date
     , p.id
 ORDER 
    BY p.post_date DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search