Support » Fixing WordPress » Custom SQL query multiple taxonomies

  • Resolved coopersita

    (@coopersita)


    I have a site with custom roles, and custom taxonomies. I’m making the advanced search, where custom posts can be filtered by roles and taxonomies.

    To accomplish this, I’m using my own custom SQL query, which works fine with the roles, and one taxonomy, but I’m having a hard time getting it to work with a second taxonomy (I get no results). Here’s the code I’m generating (with some sample values coming in from the form):

    SELECT DISTINCT p.* FROM wp_posts p
    LEFT JOIN wp_usermeta um ON p.post_author = um.user_id
    LEFT JOIN wp_term_relationships txr ON p.ID = txr.object_id
    LEFT JOIN wp_term_taxonomy tx ON txr.term_taxonomy_id = tx.term_taxonomy_id
    LEFT JOIN wp_terms trm ON tx.term_id = trm.term_id
    LEFT JOIN wp_terms trk ON tx.term_id = trk.term_id
    WHERE (um.meta_key = 'wp_capabilities' AND (um.meta_value LIKE '%student%' OR um.meta_value LIKE '%instructor%' ))
    AND (tx.taxonomy= 'mediums' AND ( trm.name LIKE '%acrylic%' OR trm.name LIKE '%oil%' ))
    AND ( tx.taxonomy= 'keywords' AND ( trk.name LIKE '%landscape%' OR trk.name LIKE '%test%'))
    AND p.post_status = 'publish'
    AND p.post_type = 'gallery'
    GROUP BY p.ID
    ORDER BY p.post_date DESC

    When I input this directly into PHPMyAdmin, I get no errors, but the thing just hangs (“Loading” message).

    What am I doing wrong?

Viewing 2 replies - 1 through 2 (of 2 total)
  • A term_relationships record has only a single term_taxonomy_id and therefore can link to only a single term. You are asking for a record that links to both trm and trk – impossible.

    Use a separate join ‘set’ for trm and trk, something like this:

    LEFT JOIN wp_term_relationships txr1 ON p.ID = txr1.object_id
    LEFT JOIN wp_term_taxonomy tx1 ON txr1.term_taxonomy_id = tx1.term_taxonomy_id
    LEFT JOIN wp_terms trm ON tx1.term_id = trm.term_id
    LEFT JOIN wp_term_relationships txr2 ON p.ID = txr2.object_id
    LEFT JOIN wp_term_taxonomy tx2 ON txr2.term_taxonomy_id = tx2.term_taxonomy_id
    LEFT JOIN wp_terms trk ON tx2.term_id = trk.term_id
    Thread Starter coopersita

    (@coopersita)

    Sorry. It was a typo. Once I fixed that, it worked.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Custom SQL query multiple taxonomies’ is closed to new replies.