how to put a category condition with exp:query?

Question

{exp:query sql="my query"}

{/exp:query}.

This works fine.But I want to give a category condition to it.When I do it,it does not flter the data accordingly.

for example:

{exp:entrie:channel category="2"}

Answer 1

With {exp:query} you'll need to spell things out. The actual query, will be dependent on what other data you are pulling via the custom query, but generally speaking, you'd want to also match the category id, {cat_id} to an {entry_id}.

For example, if I wanted to return just 10 random titles from the 'office supplies' category. I'd first look up the category id for 'office supplies' and 'desks' and learn that that the ids are 20, and 120.

{exp:query "SELECT t.title, t.entry_date, p.cat_id FROM exp_channel_titles t LEFT JOIN exp_category_posts p ON t.entry_id = p.entry_id WHERE p.cat_id IN(20, 120)"}

{/exp:query}

This assumes that the original query is fairly simple, and you're just limiting the results. But if you need to display the actual category name or already have a fairly complex query, then you'll need to or want to adjust which tables you join first, and how, you may even need a subquery.

But the basics of the query will hit the exp_category_posts table, since that's the table that joins relates an entry_id to a category_id. Category names are stored in the exp_categories table, etc.