Okay, I've been at this logic going on weeks now, and I've yet to produce anything that will work.
I'm using Wordpress, so this is saved as a string (which has PHP in it) before running through a query
If you look at http://www.libertyguide.com/jobs you can see a filter. What I want to do is use AND filtering between those three 'categories', but use OR filtering in between.
For example:
If I select Academia,Law,Policy,Full-time,Part-time,Early-Career, I expect to get a post that matches this filtering logic:
Post has (academia OR law OR policy) AND (full-time OR part-time) AND (early-career).
So essentially I want to grab posts that match academia, law, or policy, and then filter out using full-time or part-time, and finally finish with checking if it has early-career.
Here's an example of the query that I run using the above example:
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
WHERE
wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy') AND
wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='full-time' OR slug='part-time') AND
wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='early-career') AND... //Rest of query/irrelevant
This hasn't produced any results. I've also tried a query like this (first 5 lines are left out as they are the same as above example):
WHERE
wp_term.slug IN ('academia','law','policy') AND
wp_term.slug IN ('full-time','part-time') AND
wp_term.slug IN ('early-career') AND ... //Rest of query
This returns results ONLY if one 'category' has selected items. It doesn't work across categories. I still think I have the query slightly wrong. I've gotten a few solutions before (both of these are solutions that I believe have gotten me the closest), but they have loopholes.
Please don't have me use HAVING COUNT because I might as well make the whole thing an AND filter in that case, which isn't what I want.
ANY help will be greatly appreciated, and if it works, I wouldn't mind working out something to make up for the trouble.
Thanks!
Taking just the WHERE clause, we can see some problems:
WHERE wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy')
AND wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='full-time' OR slug='part-time')
AND wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='early-career')
Here we have single column (wp_term_taxonomy.term_id) that has to be simultaneously the same as the term ID for one of Academia, Law or Policy (presumably 3 distinct values) and also the same as the term ID for one of Full-Time or Part-Time (presumably 2 distinct values, and different from each of the 3 previous values) and also the same as the term ID for Early-Career (one value, but distinct from each of the previous 5 values. So, the single term ID has to be 3 different values at once, and it can't manage it.
You're likely to need to join with the wp_term_taxonomy table multiple times, using 3 different aliases.
WHERE wtt1.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy')
AND wtt2.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='full-time' OR slug='part-time')
AND wtt3.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='early-career')
where the 3 aliases I used are wtt1, wtt2, and wtt3. They'd be listed in the JOIN conditions.
Let's look at the select list and the FROM clause
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
Now let's disentangle some of the PHP material, leaving behind regular SQL:
SELECT *
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
LEFT JOIN wp_term_relationships AS tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms AS tm ON tm.term_id = tt.term_id
You probably do not want any left joins in here; you don't want to see posts that do not match the criteria, but using LEFT JOIN will mean that many posts get selected in this part (though all the rows are later discarded by the broken WHERE condition already discussed).
A single post may have multiple term relationship entries. We want a post that has at least three term relationship entries: one for the Academia/Law/Policy trio, one for the Full-Time/Part-Time duo, and also Early-Career.
SELECT *
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.Post_ID
JOIN (SELECT t1.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('academia', 'law', 'policy')
) AS t1 ON p.ID = t1.Object_ID
JOIN (SELECT t1.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('full-time', 'part-time')
) AS t2 ON p.ID = t2.Object_ID
JOIN (SELECT t1.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug = 'early-career')
) AS t3 ON p.ID = t3.Object_ID
I think that may do the trick — but it's getting late and I could be completely off the wall. It certainly isn't a simple query as written.
Assuming I got the basic SQL right, you simply have to replace the table names with the PHP notation for them:
SELECT *
FROM $wpdb->posts AS p
JOIN $wpdb->postmeta AS pm ON p.ID = pm.Post_ID
JOIN (SELECT t1.Object_ID
FROM $wpdb->term_relationships AS tr
JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN $wpdb->terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('academia', 'law', 'policy')
) AS t1 ON p.ID = t1.Object_ID
JOIN (SELECT t1.Object_ID
FROM $wpdb->term_relationships AS tr
JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN $wpdb->terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('full-time', 'part-time')
) AS t2 ON p.ID = t2.Object_ID
JOIN (SELECT t1.Object_ID
FROM $wpdb->term_relationships AS tr
JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN $wpdb->terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug = 'early-career')
) AS t3 ON p.ID = t3.Object_ID
You've not said which DBMS is in use, but it is likely MySQL. If you were using Oracle, you'd have to leave the AS's out of the table aliases. Standard SQL and most other SQL DBMS are fine with the AS for the table alias. Note how the use of the $wpdb-> notation is limited by the use of the table aliases; it makes the code easier to read (though it is still not an easy read).
Untested code usually has bugs; this is no different from any other untested code.
The first test step was to run the sub-queries in the FROM clause in individually. That immediately showed that they should not be referencing t1.Object_ID; it should be tr.Object_ID in each case. There was also an extraneous right parenthesis after 'early-career'. These mistakes were easily spotted once I had a test database against which to run the (sub)queries.
SELECT *
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.Post_ID
JOIN (SELECT tr.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('academia', 'law', 'policy')
) AS t1 ON p.ID = t1.Object_ID
JOIN (SELECT tr.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('full-time', 'part-time')
) AS t2 ON p.ID = t2.Object_ID
JOIN (SELECT tr.Object_ID
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug = 'early-career'
) AS t3 ON p.ID = t3.Object_ID
With those fixes in place, the query ran and generated rows of data. You might legitimately decide you want the slugs from the three sub-queries in the results. You'd change the sub-queries to return tr.Object_ID, tm.slug. For example, this variant of the query:
SELECT p.ID, t1.slug_1, t2.slug_2, t3.slug_3, pm.meta_key
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.Post_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_1
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('academia', 'law', 'policy')
) AS t1 ON p.ID = t1.Object_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_2
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('full-time', 'part-time')
) AS t2 ON p.ID = t2.Object_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_3
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug = 'early-career'
) AS t3 ON p.ID = t3.Object_ID;
produced the following result on some test data:
1575 policy full-time early-career date_legible
1575 policy full-time early-career date_timestamp
1575 policy full-time early-career longitude
1575 policy full-time early-career date_normal
1575 policy full-time early-career url
1575 policy full-time early-career _su_rich_snippet_type
1575 policy full-time early-career _edit_last
1575 policy full-time early-career expiration-date
1575 policy full-time early-career organization
1575 policy full-time early-career latitude
1575 policy full-time early-career location
1575 policy full-time early-career _edit_lock
This shows that there is at least one post (ID = 1575) that has the three traits you require, but it also shows that you're going to have to deal with the PostMeta data more cleverly. The result rather suggests that the PostMeta is an EAV (Entity-Attribute-Value) model. This is going to require careful handling to pull useful information (such as latitude and longitude) for the given posting. Indeed, you are going to need one (possibly outer) join for each separate meta-attribute that you want to examine.
For example, to collect the latitude and longitude, if available, for the post, you'd need to write:
SELECT p.ID, t1.slug_1, t2.slug_2, t3.slug_3, p1.latitude, p2.longitude
FROM wp_posts AS p
LEFT JOIN
(SELECT Post_ID, Meta_Key AS m1_key, Meta_Value AS latitude
FROM wp_postmeta
WHERE Meta_Key = 'latitude'
) AS p1 ON p.ID = p1.Post_ID
LEFT JOIN
(SELECT Post_ID, Meta_Key AS m2_key, Meta_Value AS longitude
FROM wp_postmeta
WHERE Meta_Key = 'longitude'
) AS p2 ON p.ID = p2.Post_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_1
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('academia', 'law', 'policy')
) AS t1 ON p.ID = t1.Object_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_2
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug IN ('full-time', 'part-time')
) AS t2 ON p.ID = t2.Object_ID
JOIN (SELECT tr.Object_ID, tm.slug AS slug_3
FROM wp_term_relationships AS tr
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS tm ON tm.term_id = tt.term_id
WHERE tm.slug = 'early-career'
) AS t3 ON p.ID = t3.Object_ID;
Which produces:
1575 policy full-time early-career -33.8210366 151.1887557
Etcetera.
Change your IN to EXISTS; try it out — you will get your result.
WHERE
wp_term_taxonomy.term_id exists (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy') AND
wp_term_taxonomy.term_id exists(SELECT term_id FROM wp_terms
WHERE slug='full-time' OR slug='part-time') AND
wp_term_taxonomy.term_id exists (SELECT term_id FROM wp_terms
WHERE slug='early-career') AND... //Rest of query/irrelevant
Use OR:
wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy')
OR wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='full-time' OR slug='part-time')
OR wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='early-career') AND... //Rest of query/irrelevan
Analyzing your query, shouldn't it be merely:
wp_term_taxonomy.term_id IN (SELECT term_id FROM wp_terms
WHERE slug='academia' OR slug='law' OR slug='policy'
OR slug='full-time' OR slug='part-time'
OR slug='early-career')