How to left join meta in queries

Question

How to left join meta in queries

Leo

2022年4月21日 21:33

I have a query something like

  //read products with thumbnail and sku
  SELECT products.*, pm1.meta_value as sku, p1.guid as thumbnail
  FROM `wp_posts` as products
  LEFT JOIN `wp_postmeta` as pm1 ON products.ID = pm1.post_id 
  LEFT JOIN `wp_postmeta` as pm2 ON products.ID = pm2.post_id 
  LEFT JOIN `wp_posts` as p1 ON pm2.meta_value = p1.ID
  WHERE products.post_type = 'product' AND pm1.meta_key = '_sku' AND pm2.meta_key = '_thumbnail_id';

This Query will get all the products that have a sku and a thumbnail but will not get any products who are missing a sku or thumbnail.

How do I get all products and the sku if it exists and the thumbnail if it exists?

I do not want to have to run 3 queries and merge the data.

Topic wpdb Wordpress sql

Category Web

Leo · Accepted Answer · 2022年4月21日 21:26

One possible solutions is use Queries in the joins instead of tables.

so the query would look something like

//read products with thumbnail and sku
  SELECT products.*, skus.sku, thumbs.thumbnail
  FROM `wp_posts` as products
  LEFT JOIN (
    SELECT posts2.ID, pm1.meta_value as sku
    FROM `wp_posts` as posts2
    LEFT JOIN `sf_postmeta` as pm1 ON posts2.ID = pm1.post_id 
    WHERE pm1.meta_key = '_sku'
  ) as skus ON products.ID = skus.ID 
  LEFT JOIN (
    SELECT posts3.ID, p1.guid as thumbnail
    FROM `wp_posts` as posts3
    LEFT JOIN `sf_postmeta` as pm2 ON posts3.ID = pm2.post_id 
    LEFT JOIN `sf_posts` as posts4 ON pm2.meta_value = posts4.ID
    WHERE  pm2.meta_key = '_thumbnail_id'
  ) as thumbs ON products.ID = thumbs.ID 
  WHERE products.post_type = 'product';

How to left join meta in queries

About