Thread

I am seeing unexpected results in a weblog call:

{exp:weblog:entries weblog="photos" category="not 2680" orderby="date" 
sort="desc" limit="50" status="not closed|preview|not_home" 
disable="pagination|member_data|trackbacks|categories" backspace="1" dynamic="off"}

I expect to get a list of 50 entries none of which are in the category 2680.

Instead some entries returned do have that id.

When I turn on MySQL debugging I see the following query:

SELECT DISTINCT(t.entry_id) 
FROM exp_weblog_titles AS t 
LEFT JOIN exp_weblogs ON t.weblog_id = exp_weblogs.weblog_id 
LEFT JOIN exp_members AS m ON m.member_id = t.author_id 
LEFT JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id 
LEFT JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id WHERE t.entry_id !='' AND
 t.site_id IN ('1') AND
 t.entry_date < 1277760414 AND
 (t.expiration_date = 0 OR t.expiration_date > 1277760414) AND
 exp_weblogs.is_user_blog = 'n' AND
 t.weblog_id = '43' AND
 (exp_categories.cat_id != '2680' OR exp_categories.cat_id IS NULL) AND
 t.status NOT IN ('closed','preview','not_home')
ORDER BY t.sticky desc, t.entry_date desc, t.entry_id desc LIMIT 0, 50

I believe that the issue here is that the SELECT DISTINCT returns just one distinct title row across the joined tables.  This is not restrictive enough.  My SQL is not strong enough to write the proper query quickly, but essentially it is not enough to assure that the row that is returned does not have the excluded category.  No record with that entry_id should have the excluded category for the row to be returned.

entry_id 1 is assigned to category 10
entry_id 2 is assigned to category 2680
entry_id 3 is assigned to category 10 and 2680

The expected result would be to return entry_ids 1 only.

The actual result is that entry_id 1 is returned and there is a chance that entry_id 3 will be returned as well, depending if the distinct row selected happens to be row 1 or row 3!

I believe for this query to execute as expected SELECT distinct must be performed on a concatenation of entry_id and exp_categories.cat_id.

We are running EE 1.6.8, not the latest build, tho.  Have looked through change log but don’t see any subsequent fixes of this issue.

Hi, Peter -

What you are seeing is the correct behavior.  If an entry is in more than one category and any of those categories are not excluded, the entry will appear.

entry_id 1 is assigned to category 10
entry_id 2 is assigned to category 2680
entry_id 3 is assigned to category 10 and 2680

The expected result would be to return entry_ids 1 only.

When using category=“not 2680”, by the code, the expected result would be to return entry 1 and 3.

You are welcome to post a feature request to see this behavior changed in the future.

Thanks Sue.  Am adding a note in the documentation…

Glad to see Lisa was able to help 😊 please post again as needed.