0

Note: This question is related to a WordPress specific question but I wanted to have an "outside look" at this from a pure SQL point of view: https://wordpress.stackexchange.com/questions/55263/order-posts-by-custom-field-and-if-custom-field-is-empty-return-remaining-posts

Let's say we have to tables with the following strucure:

Tabe posts: ID (key), Title

Table post_metadata: post_ID(FKEY), meta_key, meta_value

And I want to retrieve ID and Title of posts that have:

  • an entry in post_metadata with key = 'meta_1' and meta_value = 'value_1'
  • AND an entry in post_metadata with key = 'meta_2' and meta_value = 'value_2'

I want to order the results by the value of a third metadata with meta_key = "meta_3".

Now here is the tricky part:

Not all posts have an entry in post_metadata table with 'meta_3' as meta_key. Since Im not filtering posts by meta_3, only ordering, I wanted to keep these posts in my results, as if they had an empty value for this meta.

How can we achieve that?

Thanks

Edit:

There is SQL fiddle now: https://www.db-fiddle.com/f/kBNaaRFB5xfna5MniuTpaG/1

Leo Germani
  • 320
  • 1
  • 2
  • 8

1 Answers1

0

Perhaps:

Use an Left join once to get meta 3 value if it exists ensuring you keep all posts that have meta1 and meta2 with desired values. and then use an exist and having to ensure you only get records having both meta1 and 2 with desired values.

UNTESTED...

SELECT P.ID, P.Title, PM.Meta_value
FROM Posts P
LEFT JOIN Post_MetaData PM
 on P.ID = PM.Post_ID
and PM.key = 'meta_3'
WHERE exists (SELECT 1 
              FROM post_meta 
              WHERE ((Key 'meta_1' and meta_value = 'Value_1') OR
                    (Key 'meta_2' and meta_value = 'Value_2'))
                and P.ID = Post_ID  --Either here or an AND in the HAVING clause...need to test to know

              GROUP BY  Post_ID
              HAVING count(*) = 2 )
ORDER BY -PM.meta_value desc, P.ID

This does assume that post_metaData has a unique constraint on the key per Post_ID. otherwise we could get meta_1 with value a and meta1 with value a and the count(*) would be 2; and incorrectly return it in the results.

To ensure nulls are last follow this approach; MySQL Orderby a number, Nulls last

Doing this as an IN.... but would be slower I would think.

SELECT P.ID, P.Title, PM.Meta_value
FROM Posts P
LEFT JOIN Post_MetaData PM
 on P.ID = PM.Post_ID
and PM.key = 'meta_3'
WHERE P.ID in (SELECT Post_meta.Post_ID
              FROM post_meta 
              WHERE ((Key 'meta_1' and meta_value = 'Value_1') OR
                    (Key 'meta_2' and meta_value = 'Value_2'))
              GROUP BY  Post_ID
              HAVING count(*) = 2 )
ORDER BY -PM.meta_value desc, P.ID
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Could not make it.. Can you please elaborate a liitle bit more on th SELECT inside the exists clause? SELECT 1 ? – Leo Germani Dec 11 '18 at 17:38
  • can you put together a sample posts table and post_metaData in a SQLFiddle.com or rextester.com example that we could play with? There we can ensure it works. To answer your question: the inner select is called a correlated subquery. It returns all records where key is meta_1 and value_1 or meta_2 and value_2. It then groups by post_ID only only returns those that have a both OR condtions met.... Now the exists ensures we only return posts and meta data that exist in teh inner query (it's like an IN statement but usually faster) – xQbert Dec 11 '18 at 18:05
  • Hi, thanks for the replies. I have set up a fiddle with the suggestions here. What happens is the the posts are partially ordered, but they are always included at the end of the results, even if a change order to DESC: https://www.db-fiddle.com/f/kBNaaRFB5xfna5MniuTpaG/1 – Leo Germani Dec 13 '18 at 12:48
  • Where do you want the nulls to be; last? https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last Updated my answer assuming this. – xQbert Dec 13 '18 at 13:36
  • Thanks @xQbert, it almost worked perfectly. It did work well for ASC ordering, bu if I changed to DESC, the results were not correctly orderered. And since you mentioned in the post, actually there can be more than one value for the same meta and post. Once I did that, it stopped working well... Ive updated the fiddle with your last answer and this change in the sample data: https://www.db-fiddle.com/f/kBNaaRFB5xfna5MniuTpaG/3 – Leo Germani Dec 13 '18 at 17:17
  • The updated fiddle shows no data. Alternatively we could use a case expression `order by case when Pm.MetaValue is null then 1 else 0 end, PM.Meta_value, P.ID` Though this can't use an index This will ensure Nulls are always last and if you order PM.Meta_value asc or desc it will operate as expected. to address the other issue, I need to understand the data better. – xQbert Dec 13 '18 at 18:48