Serendipity Forums

Posted: **Sat Nov 19, 2005 6:34 am**

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.

Posted: **Mon Nov 21, 2005 12:44 pm**

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

Posted: **Tue Nov 22, 2005 3:51 am**

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.

Posted: **Tue Nov 22, 2005 11:43 am**

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

Posted: **Tue Nov 22, 2005 5:52 pm**

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.

Posted: **Tue Nov 22, 2005 6:23 pm**

Hi!

judebert wrote: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

Posted: **Tue Nov 22, 2005 7:03 pm**

And the student was enlightened. Thanks, Garvin!

Serendipity Forums

My plugin's SQL selecting only single category entries

My plugin's SQL selecting only single category entries

Re: My plugin's SQL selecting only single category entries