Trying to select ONLY posts that DON’T have a category - Can someone write me a SQL script?
Development and Programming
I’ve spent all day (literally) trying every possible combination and order of channel entries, channel categories, category archives, and query module tags I could think of in an attempt to select JUST the posts from a specific channel that are uncategorized.
After hours of research and trial and error, I’ve come to the conclusion that I need a SQL script; however, while I’m pretty good at single-table stuff, I’m downright inept at multi-table SQL commands.
Could someone write me (or explain how to make) a quick SQL script to run in the Query module that selects posts from the database that are uncategorized? I know what needs to be done - select the posts from the exp_channel_data table that are in the chosen channel, then ignore those that have entry_id’s that coincide with those found in the exp_category_posts table. The problem is that I don’t know how to write a dual-table script.
Any help would be most appreciated - thnks!
[Mod Edit: Moved to the forum Community Help]
Try using:
{exp:channel:categories backspace="1" channel="your_channel"}
{category_id}|
{/exp:channel:categories}This should give you a pipe separated list of ids. Then you should somehow be able to feed this into the channel:entries category=”” parameter. something like
{exp:channel:entries channel="your_channel" category="not {exp:channel:categories backspace="1" channel="your_channel"}{category_id}|{/exp:channel:categories}"}That probably wont work due to parse order issues but there will be a way to make it work. You just need to get the list of cat id before the channel entries tag is parsed.
We’ve got a free string plugin that allows you to set/get strings from different places in the template. We haven’t released it yet but let me know if you want to try it. Our developers aren’t in at the moment (4am on a Sunday morning) so apologies I can’t help any more right now!
This should get you all the entry_ids of un-categorized entries in a particular channel:
SELECT entry_id FROM exp_channel_titles WHERE entry_id NOT IN (SELECT entry_id FROM exp_category_posts GROUP BY entry_id) AND channel_id = {channel_id}This should get you all the entry_ids of un-categorized entries in a particular channel:SELECT entry_id FROM exp_channel_titles WHERE entry_id NOT IN (SELECT entry_id FROM exp_category_posts GROUP BY entry_id) AND channel_id = {channel_id}
Thank you!!! This is exactly what I needed - works beautifully! Thank you so much!
Your welcome.