[thelist] SQL: conditional ANDs and ORs. Totally stumped.
Francis Marion
francis.marion at sfroy.com
Fri Mar 30 13:33:45 CDT 2012
Yeah, sorry, it was a my-mind-is-stuffed post.
1) While I showed only three groupings, this form is built dynamically, so it may contain only 1 parent, or many. In fact, parents per-se will never be searched, they are merely parents of children to be used as search criteria. A parent will always have a parent of 0 (zero). Also, this will never exceed two levels. Fruit (just a title) -> Apple. That's as deep as it goes.
2) You are dead right, I was so focussed on one part that I forgot to mention the rest of it. See the highlighted bit that I'm trying to replace.
Sorry if I'm doing a bit of a dump here, my mind is clogged on this subject. Often the act of clarifying to ask a question brings the answer.
This is my query as is:
SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_type #abbreviated the columns
FROM wp_posts
INNER JOIN wp_term_relationships
ON ( wp_posts.id = wp_term_relationships.object_id )
INNER JOIN wp_term_relationships AS tt1
ON ( wp_posts.id = tt1.object_id )
INNER JOIN wp_term_relationships AS tt2
ON ( wp_posts.id = tt2.object_id )
WHERE 1 = 1
AND wp_posts.post_type = 'sfr_place'
AND ( wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'
)
#=============================================
# This is what I'm trying to group into ANDs and ORs
#=============================================
# AND ( wp_term_relationships.term_taxonomy_id IN ( 15 )
# OR tt1.term_taxonomy_id IN ( 16 )
# OR tt2.term_taxonomy_id IN ( 18 )
# )
#=============================================
GROUP BY wp_posts.id
ORDER BY wp_posts.post_date DESC
#LIMIT 0, 10;
This is currently an example of what it spits out. If I feed it check one box in each grouping, my query string will offer me 15,16,18, this is what gets spit out. Everything is an OR. In this particular case I'd want it to be 15 AND 16 AND 18. Had I selected more than one from each sub cat I might want to search for (14 OR 15) AND (16 OR 17) AND (18 OR 20).
+-----+---------------------------------------+-----------+
| ID | post_title | post_type |
+-----+---------------------------------------+-----------+
| 236 | Garage Kekport - Trois-Rivières | sfr_place |
| 179 | Drummondville - Propriété 009 | sfr_place |
| 178 | Drummondville - Propriété 008 | sfr_place |
| 177 | Nicolet - Propriété 008 | sfr_place |
| 176 | Drummondville - Propriété 007 | sfr_place |
| 175 | Trois-Rivières - Propriété 006 | sfr_place |
| 174 | Nicolet - Propriété 006 | sfr_place |
| 173 | Trois-Rivières - Propriété 005 | sfr_place |
| 171 | Commerce de détail - Propriété 003 | sfr_place |
| 170 | Secteur Drummondville Propriété 001 | sfr_place |
+-----+---------------------------------------+-----------+
These are the other relevant tables, just for a sense of clarity.
TERMS TABLE
SELECT term_id, name, slug FROM wp_terms;
+---------+----------------------------+---------------------------+
| term_id | name | slug |
+---------+----------------------------+---------------------------+
| 1 | Uncategorized | uncategorized |
| 2 | Blogroll | blogroll |
| 3 | Menu Primaire | menu-primaire |
| 4 | Menu Secondaire | menu-secondaire |
| 5 | Nos réalisations | nos-realisations |
| 6 | Projets en développement | projets-en-developpement |
| 9 | Superficie | superfici |
| 8 | vedette | vedette |
| 10 | Ville | ville |
| 11 | Type | type |
| 12 | 0 à 2 000 pi2 | 0-2k-pi2 |
| 13 | 2000 à 10000 pi2 | 2k-10k-pi2 |
| 14 | 10000 à 20000 pi2 | 10k-20k-pi2 |
| 15 | 20000 pi2 et plus | 20k-pi2-plus |
| 16 | Trois-Rivières | trois-rivieres |
| 17 | Drummondville | drummondville |
| 18 | Bureau | bureau |
| 19 | Commercial | commercial |
| 20 | Nicolet | nicolet |
| 21 | Type pour aucun résultats | type-pour-aucun-resultats |
+---------+----------------------------+---------------------------+
TAXONOMY TABLE
SELECT term_taxonomy_id, term_id, taxonomy, parent, count FROM wp_term_taxonomy;
+------------------+---------+----------------------+--------+-------+
| term_taxonomy_id | term_id | taxonomy | parent | count |
+------------------+---------+----------------------+--------+-------+
| 1 | 1 | category | 0 | 10 |
| 2 | 2 | link_category | 0 | 7 |
| 3 | 3 | nav_menu | 0 | 7 |
| 4 | 4 | nav_menu | 0 | 4 |
| 5 | 5 | post_tag | 0 | 2 |
| 6 | 6 | post_tag | 0 | 4 |
| 9 | 9 | sfr_places_categorie | 0 | 1 |
| 8 | 8 | post_tag | 0 | 0 |
| 10 | 10 | sfr_places_categorie | 0 | 0 |
| 11 | 11 | sfr_places_categorie | 0 | 0 |
| 12 | 12 | sfr_places_categorie | 9 | 3 |
| 13 | 13 | sfr_places_categorie | 9 | 2 |
| 14 | 14 | sfr_places_categorie | 9 | 2 |
| 15 | 15 | sfr_places_categorie | 9 | 1 |
| 16 | 16 | sfr_places_categorie | 10 | 3 |
| 17 | 17 | sfr_places_categorie | 10 | 4 |
| 18 | 18 | sfr_places_categorie | 11 | 4 |
| 19 | 19 | sfr_places_categorie | 11 | 5 |
| 20 | 20 | sfr_places_categorie | 10 | 3 |
| 21 | 21 | sfr_places_categorie | 11 | 0 |
+------------------+---------+----------------------+--------+-------+
RELATIONSHIP TABLE
SELECT object_id, term_taxonomy_id FROM wp_term_relationships;
+-----------+------------------+
| object_id | term_taxonomy_id |
+-----------+------------------+
| 170 | 17 |
| 170 | 18 |
| 171 | 13 |
| 171 | 19 |
| 171 | 20 |
| 173 | 15 |
| 173 | 16 |
| 173 | 18 |
[snip]
| 236 | 16 |
| 250 | 3 |
+-----------+------------------+
On 2012-03-30, at 11:22 AM, Matt Warden wrote:
> On Fri, Mar 30, 2012 at 10:44 AM, Francis Marion
> <francis.marion at sfroy.com> wrote:
>> I want to create a query on that table that does the following:
>>
>> For each SUB category, the relationship should be an OR, but for each group of sub categories, the relationship should be an AND. In the above example, I would do a search for:
>>
>> (Apple OR Orange) AND (Carrot) AND (Beef OR Chicken)
>>
>>
>>
>> Suggestions on how to approach this?
>
> If I understand your situation correctly, and I do think I am missing
> an important detail (explained below), I think you want a series of
> EXISTS clauses. Based on what you laid out, I don't think you can do a
> series of ANDs and ORs directly, because there is no single row that
> will satisfy all those conditions. You are looking for a number of
> rows that satisfy your conditions, connected together by some
> [relationship condition].
>
> where
> exists (select .... where [relationship condition] and term_id in ($fruitcsv))
> and exists (select .... where [relationship condition] and term_id
> in ($vegcsv))
> and exists (select .... where [relationship condition] and term_id
> in ($meatcsv))
>
> The part I am missing from your explanation is the [relationship
> condition], because the table you showed is just a list of
> subcategories. Without the relationship condition, all you are really
> confirming is that the subcategories in $fruitcsv, $vegcsv, and
> $meatcsv actually exist in the table. That is not very useful,
> particularly because you probably built the list from that table in
> the first place. So, the above comes with the caveat that the nature
> of the relationship condition could actually change what the best way
> to approach this is.
>
> --
> Matt Warden
> http://mattwarden.com
>
>
> This email proudly and graciously contributes to entropy.
> --
>
> * * Please support the community that supports you. * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
--
Francis Marion
Département communications interactives
Signé François Roy
819 336-6746 x 236
1 888-235-1175, poste 236
francis.marion at sfroy.com
More information about the thelist
mailing list