My plugin's SQL selecting only single category entries

[judebert]

I'm writing a plugin to move all entries of a particular category from the mainpage listing to a separate box. I consider it a "newsbox".

I'm taking baby steps. First step: remove the entries of that category from the main listing.

Keeping in mind that I'll eventually want the newsbox to contain multiple categories, I added this SQL to the 'and' attribute in the frontend_fetchentries hook:

Code: Select all

(NOT EXISTS 
  (SELECT 'x' FROM serendipity_entries 
     WHERE ec.entryid = e.id AND ec.categoryid IN (40))
)

Sure enough, the entries in that category (let's call it "Programming" to make things easier) are removed from the frontpage.

However, entries in multiple categories including "Programming" (like "Programming", "Java", "Games") are *not* removed from the listing.

What in the world is going on? I'm reasonably new to SQL, so I must be missing something, but four hours of debugging efforts haven't helped. Maybe you guys can point out the obvious point I'm overlooking.

[garvinhicking]

I think you must exclude the entry ID like this:

Code: Select all

AND e.id NOT IN (SELECT id FROM serendipity_entrycat WHERE categoryid = ec.categoryid)

Or something like that. It definitely is a bit hard, because the SQL statement does contain multiple joins and relations...

Best regards,
Garvin

[judebert]

D*mnit, Garvin stop making sense!

Works like a charm. And it's got me wondering why I didn't think of it myself.

It's also got me wondering: why do we do all those joins? We don't select from _entrycat, _category, _authorgroups, or _access, but we LEFT JOIN each of them anyway. Wouldn't it be more efficient to just alias them in the FROM instead of joining them?

I'm sure you've got a reason. I'm just too much of an SQL n00b to understand it, so I seek enlightenment.

[garvinhicking]

Aliasing tables with a "from" is the same as a straight join. But since an entry mustn't have a category association, and mustn'b be restircted with permissions, we need LEFT Joins, that only conditionally link a table.

With a straight join, the select would yield zero results, if an entry had no category association.

Best regards,
Garvin

[judebert]

Interesting. Since we don't SELECT from those tables anyway, do we even need to JOIN them at all?

Incidentally, if anyone else is planning on copying the code above for their own nefarious purposes (yeah, right), note that a minor misspelling occurred. The corrected code is:

Code: Select all

AND e.id NOT IN (SELECT entryid FROM serendipity_entrycat WHERE categoryid = the_cat)

I suspect "categoryid IN (cat1, cat2)" would work, too, but I've already mentioned I'm an SQL n00b, so check first.

[garvinhicking]

Hi!

Interesting. Since we don't SELECT from those tables anyway, do we even need to JOIN them at all?

We do need those joins in case there IS a relation set up which restricts the entry. So if a read/write permission is set, it needs to be JOINed and checked if the right permissions apply.

Best regards,
Garvin

[judebert]

And the student was enlightened. Thanks, Garvin!