[wordpress] [wp-hackers] Multiple category exclusion query
Brian Dupuis
wordpress at coldforged.org
Wed Mar 9 00:52:13 GMT 2005
I admit defeat... I can't get a clean query to save my life. I can only
get a query which would theoretically work by using temporary tables as
I was fearing. Using that temporary table and the subsequent goofy
$whichcat and $join clauses that result break everything else that deals
with those clauses. The general gist of the problem is that you can't
simply perform a nice binary logical operation to get the needed rows.
Well, you could if you could use a subquery with NOT EXISTS, but we want
to be nice to the 4.0 MySQL users and so subqueries are out. Why?
Because with exclusions you want only post IDs which have _no rows_ that
contain an excluded category. Try doing that with a simply AND NOT clause.
So, for posterity before I burn it and the computer that it's on from
frustration, here's the closest I got. Imagine this in the appropriate
place in classes.php. Note also that to even get it to try to work,
you'll have to qualify some column names in later code since these
queries add ambiguities.
Now if I never see another SELECT statement in about a decade, I'll be
happy.
// Category stuff
if ((empty($q['cat'])) || ($q['cat'] == '0') ||
// Bypass cat checks if fetching specific posts
( $this->is_single || $this->is_page )) {
$whichcat='';
} else {
$q['cat'] = ''.urldecode($q['cat']).'';
$q['cat'] = addslashes_gpc($q['cat']);
$join = " LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID =
$wpdb->post2cat.post_id) ";
$cat_array = preg_split('/[,\s]+/', $q['cat']);
// Support multiple inclusions and exclusions.
$inclusions = Array();
$exclusions = Array();
foreach( $cat_array as $category ) {
if( ( $category_int = intval( $category ) ) < 0 ) {
$exclusions[] = abs($category_int);
$children_string = get_category_children(
$category_int );
if( !empty( $children_string ) ) {
$exclusion_children = explode('/',
ltrim($children_string,'/') );
$exclusions = array_merge( $exclusions,
$exclusion_children );
}
} else {
$inclusions[] = $category_int;
$children_string = get_category_children(
$category_int );
if( !empty( $children_string ) ) {
$inclusion_children = explode('/',
ltrim($children_string,'/') );
$inclusions = array_merge( $inclusions,
$inclusion_children );
}
}
}
$whichcat = '';
// Case for both inclusions and exclusions
if( ( count( $exclusions ) > 0 ) && ( count( $inclusions ) >
0 ) )
{
$subquery = "DROP TABLE IF EXISTS wpexcludes";
$wpdb->query( $subquery );
$subquery = "CREATE TEMPORARY TABLE wpexcludes SELECT * " .
" FROM $wpdb->posts " .
" LEFT JOIN $wpdb->post2cat " .
" ON ( $wpdb->posts.ID = $wpdb->post2cat.post_id ) " .
" WHERE category_id IN (
".implode(',',$exclusions)." ) " .
" GROUP BY ".$wpdb->posts.".ID";
$wpdb->query( $subquery );
$join = " LEFT JOIN $wpdb->post2cat ON ( $wpdb->posts.ID
= $wpdb->post2cat.post_id ) LEFT OUTER JOIN wpexcludes ON (
$wpdb->posts.ID = wpexcludes.ID )";
$whichcat .= " AND wpexcludes.ID IS NULL AND
$wpdb->post2cat.category_id IN (".implode(',',$inclusions).") ";
} else if( count( $inclusions ) > 0 ) {
$whichcat .= " AND $wpdb->post2cat.category_id IN
(".implode(',',$inclusions).") ";
} else {
if( count( $exclusions ) )
$whichcat .= " AND $wpdb->post2cat.category_id NOT IN
(".implode(',',$inclusions).") ";
}
}
Brian Dupuis wrote:
> Thanks for the response Elliot. Yes, you are correct about your
> queries... and that's the path I had gotten down and had already
> created some decent code to get those style of queries out the back
> end. I was making the problem too difficult in my brain. See, I
> thought the wp_post2cat table was a list of _additional_ categories
> that a post was posted to. With that kind of misunderstanding,
> creating a query that would be capable of not including a row in the
> wp_posts table if there was a row found in the wp_post2cat table that
> had an excluded category was driving me insane. Thankfully that's not
> the case and I should be able to get a query going in short order.
>
> In essence, I wasn't using wp_post2cat as it was meant to be used and
> hence the queries I was generating didn't work.
>
> Thanks for your input!
> Regards,
> Brian
>
> Elliott Bäck wrote:
>
>> I wrote a SQL search engine once that had to parse user input,
>> including quotes and negation, and it generates slightly different
>> SQL than what you're describing. See, the grouping you have isn't
>> worth it. You do don't have to be that smart. If you have 1 -2 3
>> -29 30, just write:
>>
>> 1 AND NOT 2 OR 3 AND NOT 29 OR 30
>>
>> I am assuming that the precedence will order these:
>>
>> ((((1 ^ !2) + 3) ^ !29) + 30)
>>
>> But according to RedHat
>> (http://www.redhat.com/docs/manuals/database/RHDB-7.1.3-Manual/sql/sql-precedence.html),
>> AND binds more tightly, so we really have:
>>
>> (1 ^ (!2 + (3 ^ (!29 + 30))))
>>
>> What does work is to write:
>>
>> ((1 + 3 + 30) ^ !(2 + 29))
>>
>> Just factor into two groups, positive and negative, and take the
>> negative conjunction of the OR'ed positive elements with the OR'ed
>> negative elements. Does that help? I guess not...what you want is
>> code to do this:
>>
>> function giveMeSQL($in){
>> $pos = array();
>> $neg = array();
>>
>> foreach($number in $in){
>> if($in > 0){
>> $pos[] = $number;
>> } else{
>> $neg[] = $number;
>> }
>> }
>>
>> $sql = "";
>> if(count($pos) > 0){
>> $sql .= "("; for($i = 0; $i < count($pos); $i++){
>> $sql .= $pos[$i];
>> if($i < count($pos) - 1)
>> $sql .= " OR ";
>> }
>>
>> $sql .= ")";
>> }
>> if(count($neg) > 0){
>> $sql .= "AND NOT ("; for($i = 0; $i < count($neg);
>> $i++){
>> $sql .= $neg[$i];
>> if($i < count($neg) - 1)
>> $sql .= " OR ";
>> }
>>
>> $sql .= ")";
>> }
>>
>> return $sql;
>> }
>>
>> Is that what you're wondering?
>>
>> Thanks,
>> Elliott C. Bäck
>>
>> 607-229-0623
>> http://elliottback.com
>>
>>
>>
>> Brian Dupuis wrote:
>>
>>> I've used a hacked classes.php forever to get my multiple category
>>> exclusions that I need for my site. It's a naive method where you
>>> either get inclusions or exclusions, nothing more, but it works for
>>> my application. I was getting tired of keeping the hacked version
>>> and figured I had some energy to invest in getting a "real" version
>>> [where "real" means that you can specify $cat strings of, for
>>> instance, "1,2,3,-29,-30" so that you'll get, in pseudocode, ((1 OR
>>> 2 OR 3) AND NOT (29 or 30)) so that you'll exclude items that belong
>>> to 1 and 29] going for possible inclusion in WordPress, so in my
>>> spare time I've been trying to get it up. As such, I've wracked my
>>> brain trying to come up with a query that does the necessary job and
>>> have failed. Utterly. The only things I can come up with are either
>>> subqueries (which aren't supported in MySQL 4.0) and temporary
>>> tables (which I'm afraid would negatively impact performance).
>>>
>>> Has anyone else tried to crack this nut? Is it even _worth_ trying
>>> to crack? I see a couple of questions pop up every now and then
>>> about people trying to exclude categories and sometimes multiple,
>>> but not often. Mostly people recommend doing inclusion of every
>>> category but the ones you want excluded which works in some ways but
>>> doesn't in others and is a pain to deal with for any quantity of
>>> categories (my exclusions are 27 and 28).
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>>
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>>
>>
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
>
>
More information about the wp-hackers
mailing list