Custom WP query with polylang term

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Revised 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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0