SQL IN AND IN无法正常工作

Question

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!

Answer 1

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

---

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