[wordpress] [wp-hackers] Multiple category exclusion query

[Brian Dupuis]

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
>
>
>