Custom WP query with polylang term

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