i am banging my head on how should the mysql query be correctly defined. I have term_taxonomy_id id defined for polylang languages 2 and 9 I want to return ONLY return posts, who has term_taxonomy_id 2 (primary language) AND then from the result all posts who has term_taxonomy_id 32,71,38. Can you help me on this? Where i am wrong on this query?
SELECT SQL_CALC_FOUND_ROWS
agn_posts.ID,
agn_posts.post_title,
agn_term_relationships.term_taxonomy_id
FROM
agn_posts
LEFT JOIN agn_term_relationships ON(
agn_posts.ID = agn_term_relationships.object_id
)
WHERE
1 = 1
AND(
agn_term_relationships.term_taxonomy_id IN(32)
OR agn_term_relationships.term_taxonomy_id IN(71)
OR agn_term_relationships.term_taxonomy_id IN(38)
)
AND agn_term_relationships.term_taxonomy_id IN(2)
AND agn_posts.post_type = 'property' AND(
agn_posts.post_status = 'publish' OR agn_posts.post_status = 'private'
)
GROUP BY
agn_posts.ID
ORDER BY
RAND()
LIMIT 0, 50
i am banging my head on how should the mysql query be correctly defined. I have term_taxonomy_id id defined for polylang languages 2 and 9 I want to return ONLY return posts, who has term_taxonomy_id 2 (primary language) AND then from the result all posts who has term_taxonomy_id 32,71,38. Can you help me on this? Where i am wrong on this query?
SELECT SQL_CALC_FOUND_ROWS
agn_posts.ID,
agn_posts.post_title,
agn_term_relationships.term_taxonomy_id
FROM
agn_posts
LEFT JOIN agn_term_relationships ON(
agn_posts.ID = agn_term_relationships.object_id
)
WHERE
1 = 1
AND(
agn_term_relationships.term_taxonomy_id IN(32)
OR agn_term_relationships.term_taxonomy_id IN(71)
OR agn_term_relationships.term_taxonomy_id IN(38)
)
AND agn_term_relationships.term_taxonomy_id IN(2)
AND agn_posts.post_type = 'property' AND(
agn_posts.post_status = 'publish' OR agn_posts.post_status = 'private'
)
GROUP BY
agn_posts.ID
ORDER BY
RAND()
LIMIT 0, 50
Share
Improve this question
asked Oct 30, 2020 at 19:46
jamjam
1732 gold badges3 silver badges15 bronze badges
4
|
1 Answer
Reset to default 3Revised Answer
If the terms 2
, 32
, 71
and 38
belong in the same taxonomy like language
, then see my original answer.
Otherwise, the WP_Query
would be like so:
$query = new WP_Query( [
'post_type' => 'property',
'tax_query' => [
'relation' => 'AND', // for clauses 1 and 2
[ // clause 1
'taxonomy' => 'language',
'terms' => 2,
],
[ // clause 2
'relation' => 'OR', // for sub-clauses 1, 2 and 3
[ // sub-clause 1
'taxonomy' => 'taxonomy-name',
'terms' => 32,
],
[ // sub-clause 2
'taxonomy' => 'taxonomy-name-2',
'terms' => 71,
],
[ // sub-clause 3
'taxonomy' => 'taxonomy-name-3',
'terms' => 38,
],
],
],
] );
That should give you the correct IN() AND ( IN() OR IN() OR IN() )
query, and as for the SQL command, just do echo $query->request;
and then do whatever needed with the command. :)
And be sure to replace the taxonomy names with the correct ones.
Original Answer
Note that I did not test your code on GitHub, but what you're trying to do:
Query posts in that are assigned to the term
2
And then those assigned to the terms
32
,71
,38
Can be easily achived in WP_Query
via the tax_query
parameter, like so:
$query = new WP_Query( [
'post_type' => 'property',
'tax_query' => [
'relation' => 'AND', // AND is the default
[
'taxonomy' => 'language',
'terms' => 2,
],
[
'taxonomy' => 'language',
'terms' => [ 32, 71, 38 ],
],
],
] );
And if you inspect the SQL command from the above query, i.e. echo $query->request
, the command has two LEFT JOIN
clauses:
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
And the WHERE
for the tax_query
would look like:
wp_term_relationships.term_taxonomy_id IN (2)
AND
tt1.term_taxonomy_id IN (32,71,38)
So that's how your SQL command should look like — i.e. use two (LEFT/INNER/etc.) JOINs with the first one for querying posts in the term 2
, and the second one for posts in the terms 32
, 71
and/or 38
.
Here's an example based on your SQL command, except (just like above), I used the prefix wp_
instead of agn_
, and I also used table aliases like p
for wp_posts
:
(Note that in this example, I intentionally didn't use the SQL_CALC_FOUND_ROWS
)
SELECT p.ID, p.post_title, tr.term_taxonomy_id
FROM wp_posts p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id
WHERE 1 = 1
AND tr.term_taxonomy_id IN(2)
AND tr2.term_taxonomy_id IN(32, 71, 38)
AND p.post_type = 'property'
AND (p.post_status = 'publish' OR p.post_status = 'private')
GROUP BY p.ID
ORDER BY RAND() # note that this can get VERY slow on large data/tables
LIMIT 0, 50
new WP_Query()
orget_posts()
? – Sally CJ Commented Oct 30, 2020 at 19:59get_posts()
instead ofWP_Query
. And what's the output ofecho $similar_properties_query->request;
? Have you tried running it via phpMyAdmin or direct$wpdb->query()
call, and did it return any results? – Sally CJ Commented Oct 31, 2020 at 1:30