2

I am trying to get the wp posts using custom query. Here is sql query:

SELECT p.*, IFNULL(SUM(vote), 0) AS vote_count, CAST(m.meta_value AS SIGNED) AS idea_count
FROM wp_posts p
INNER JOIN wp_term_relationships r ON p.ID=r.object_id
INNER JOIN wp_term_taxonomy t ON t.term_taxonomy_id=r.term_taxonomy_id AND t.taxonomy='category'
LEFT JOIN wp_wdpv_post_votes v ON v.post_id=p.ID
LEFT JOIN wp_postmeta m ON m.post_id=p.ID AND m.meta_key='ideas_count'
WHERE p.post_status='publish' AND p.post_type='post' AND t.term_id='5'
GROUP BY p.ID
ORDER BY p.ID DESC
LIMIT 0, 8

This sql query is working fine. But now i have another case, Where I want show the posts using this query but only the posts which does not have specific meta key. Meta key to filter is 

'private_spaces_post'

This could be specific to mysql query. But i will be very thankful if someone can give me a solution for this problem.

Aatif Farooq
  • 1,828
  • 1
  • 13
  • 15

1 Answers1

6

You can LEFT JOIN against a subquery which only returns those that do have private_spaces_post, and look for NULLs 

SELECT p.*, IFNULL(SUM(vote), 0) AS vote_count, CAST(m.meta_value AS SIGNED) AS idea_count
FROM wp_posts p
  INNER JOIN wp_term_relationships r ON p.ID=r.object_id
  INNER JOIN wp_term_taxonomy t ON t.term_taxonomy_id=r.term_taxonomy_id AND t.taxonomy='category'
  LEFT JOIN wp_wdpv_post_votes v ON v.post_id=p.ID
  LEFT JOIN wp_postmeta m ON m.post_id=p.ID AND m.meta_key='ideas_count'
  /* LEFT JOIN subquery returning only ids that *do* have the meta key */
  LEFT JOIN (
    SELECT post_id FROM wp_postmeta WHERE meta_key='private_spaces_post'
  ) psp ON p.ID = psp.post_id
WHERE p.post_status='publish' AND p.post_type='post' AND t.term_id='5'
  /* And find post ids from the main table that *don't* have a match in the subquery (LEFT JOIN returns NULL) */
  AND psp.post_id IS NULL
GROUP BY p.ID
ORDER BY p.ID DESC
LIMIT 0, 8
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • 1
    Do you think it will be faster if we just do it like `LEFT JOIN wp_postmeta m2 ON m2.post_id=p.ID AND meta_key='private_spaces_post'` without the sub-query – Aatif Farooq Dec 03 '12 at 18:36
  • @AatifFarooq It may be. You still need the `WHERE` clause component though. – Michael Berkowski Dec 03 '12 at 18:46
  • Can you please tell me what is difference between `post_id=NULL` and `post_id IS NULL` ? – Aatif Farooq Dec 03 '12 at 18:52
  • @AatifFarooq `NULL` is not a value, but rather the _absence of a value_. It therefore does not work properly with equality operators `=` and instead RDBMS provide the `IS NULL` construct to check for it specially. See http://stackoverflow.com/questions/2749044/what-is-null-and-is-null – Michael Berkowski Dec 03 '12 at 18:57
  • Now i got it. In-fact i used the above the left join in the query with `field=null` in where clause before posting this question and there were no results. I just thought my query was wrong. But now i realized that my syntax was wrong. By the way, thanks for all your help... :) – Aatif Farooq Dec 03 '12 at 19:02