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:
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_taxonomytable multiple times, using 3 different aliases.where the 3 aliases I used are
wtt1,wtt2, andwtt3. They’d be listed in the JOIN conditions.Let’s look at the select list and the FROM clause
Now let’s disentangle some of the PHP material, leaving behind regular SQL:
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.
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:
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).Bug Fixing and Problem Solving
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 betr.Object_IDin 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.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:produced the following result on some test data:
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:
Which produces:
Etcetera.
Change your IN to EXISTS; try it out — you will get your result.
Use OR:
Analyzing your query, shouldn’t it be merely: