WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Custom SQL query multiple taxonomies (3 posts)

  1. coopersita
    Member
    Posted 1 year ago #

    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?

  2. vtxyzzy
    Member
    Posted 1 year ago #

    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
  3. coopersita
    Member
    Posted 1 year ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic