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

[Elliott Bäck]

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
>