21

i want to retrieve custom posts using using custom query . my taxonomy is recipe_tx and terms (Beef) , (Chicken) etc in it .

i have tried using

SELECT p.* FROM wp_posts p, wp_term_taxonomy tt, wp_term_relationships tr 
WHERE p.ID=tr.`object_id` 
AND tt.`term_id`=tr.`term_taxonomy_id` 
AND (p.post_type = 'recipe_cpt')
AND p.post_status = 'publish'
AND tt.`term_taxonomy_id` = 37

but no luck .

can someone help me how to get wp posts by their term_id .

if beefs id is 37 then i want to retrieve all the posts with term_id = 37

Thank's

1

3 Answers 3

46

Have you tried using the WP_Query class? You might find it's easier to use the built-in tools for this instead of a custom query from scratch. Something similar to the following should work for you:

<?php
$args = array(
'post_type' => 'recipe_cpt',
'tax_query' => array(
    array(
    'taxonomy' => 'recipe_tx',
    'field' => 'term_id',
    'terms' => 37
     )
  )
);
$query = new WP_Query( $args ); ?>

EDIT: note the tax_query is an array of arrays by design. Many tax query problems are a result of missing this detail.

EDIT: corrected field value typo above, replacing 'id' with 'term_id'.

2
3

I know the question is answered, but if anyone is trying to achieve the same with a raw SQL query, you could try the following:

SELECT * 
FROM wpiw_posts
WHERE ID IN (
    SELECT object_id 
    FROM wpiw_term_relationships AS TR 
        INNER JOIN wpiw_term_taxonomy AS TT ON TR.term_taxonomy_id = TT.term_taxonomy_id
        INNER JOIN wpiw_terms AS T ON TT.term_id = T.term_id
    WHERE TT.taxonomy = 'recipe_tx' AND T.term_id = 37
)

The above query will get you all the posts that belong to the given taxonomy (recipe_tx) and term ID (37).

1
  • This is better because is more faster than new WP_Query or get_posts() Commented Jul 19, 2022 at 8:11
3

I know that this is an old post, but I just wanted to chime in. @jdm2112 is correct. Just use the WP_Query class. This makes sure that if the database structure is ever updated in the future, your query still works.... However, since it looks like you are looking for a raw SQL answer, this is the actual SQL that WP_Query would execute... at least as of WordPress 5.6. (I've adapted the SQL to your question)

SELECT * from wp_posts 
    LEFT JOIN 
        wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
    WHERE 
        (wp_term_relationships.term_taxonomy_id IN (37)) AND
        wp_posts.post_type = 'recipe_cpt' AND
        wp_posts.post_status = 'publish'
    GROUP BY wp_posts.ID

You can get the the actual query by creating a WP_Query instance, then look at the request property like so:

<?php

$args = [
    'post_type' => 'recipe_cpt',
    'tax_query' => [
        [
            'taxonomy' => 'recipe_tx',
            'field' => 'term_id',
            'terms' => 37
        ]
    ]
];
$queryObj = new WP_Query($args); 

// This will give you the actual raw SQL query
$rawSqlQuery = $queryObj->request;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.