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

• wp-query

• 2 Why are you not using new WP_Query() or get_posts()? – Sally CJ Commented Oct 30, 2020 at 19:59
• I am using, but the "lang" argument does not work somehow. Here github/jamajamajaaama/test/blob/main/similar-properties.php on the line 15 I added the parameter lang, but wpQuery on line 121 does not take that param somewhat. So I tried to appent tax_query as polylang keeps the languages in taxonomies on line 111, outputed the query and it did not work weel. So with that query above , I am trying to debug the code :) – jam Commented Oct 30, 2020 at 22:52
• You could try suppressing filters, e.g. use get_posts() instead of WP_Query. And what's the output of echo $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
• I tried to echo the SQL query, but it included all posts with term_taxonomy in 32.71,38 AND 2. I need to get posts who has term_tax id 32,71,38 from those who has term_tax id 2 – jam Commented Nov 1, 2020 at 17:09

1 Answer 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