How get the Pods for one taxonomy term, but without another term

Resolved

(@flexjoly)

Hi,
Getting more and more out of pods. 🙂

Now I need to show the Pods that are linked to a pod-term, but does not have another term.

The where is giving no errors, but due to the complex join structure, it does not exclude the second term.

In query-monitor the query is:

SELECT DISTINCT t.*
FROM wp_posts AS t
         LEFT JOIN wp_podsrel AS rel_exercise
                   ON rel_exercise.field_id = 84
                       AND rel_exercise.item_id = t.ID
         LEFT JOIN wp_posts AS exercise
                   ON exercise.ID = rel_exercise.related_item_id
         LEFT JOIN wp_term_relationships AS rel_ewtag
                   ON rel_ewtag.object_id = t.ID
         LEFT JOIN wp_term_taxonomy AS rel_tt_ewtag
                   ON rel_tt_ewtag.taxonomy = 'ewtag'
                       AND rel_tt_ewtag.term_taxonomy_id = rel_ewtag.term_taxonomy_id
         LEFT JOIN wp_terms AS ewtag
                   ON ewtag.term_id = rel_tt_ewtag.term_id
WHERE ( ( exercise.ID=2059 and ewtag.term_id=31 and ewtag.term_id!=32 )
    AND ( t.post_type = 'exerciseword' )
    AND ( t.post_status IN ( 'publish' ) ) )
ORDER BY t.menu_order, t.post_title, t.post_date

The where part for the terms is: ewtag.term_id=31 and ewtag.term_id!=32. First I used the slug, but I hoped using the id, would reduce the joins.

I need the pagination, else I could do a foreach on the results.

I hope someone can help me out here.
Thanks in advance, flexjoly

Viewing 1 replies (of 1 total)

Thread Starter

flexjoly

(@flexjoly)

1 week, 3 days ago

With AI I changed the where ewtag.term_id!=32 to:

NOT EXISTS (SELECT 1
FROM wp_term_relationships AS tr 
JOIN wp_term_taxonomy AS tt 
 ON tt.taxonomy = 'ewtag'
  AND tt.term_taxonomy_id = tr.term_taxonomy_id
  AND tt.term_id = 32
WHERE rel_tag_excl.object_id = t.ID )

Even more complex, but it works!! 🙂

If you want to use the slug, an extra join is needed:

NOT EXISTS ( SELECT 1
FROM wp_term_relationships AS tr 
JOIN wp_term_taxonomy AS tt 
  ON tt.taxonomy = 'ewtag'
  AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS tm 
  ON tm.term_id = tt.term_id
  AND tm.slug = 'myslug'
WHERE tr.object_id = t.ID )

It can also be done with an extra join, but I choose this one, for then it only changes the where and everything is in one place in the query.
Hope this can help someone else too.

I still hope is a better or easier solution 😉

Viewing 1 replies (of 1 total)

You must be logged in to reply to this topic.

Tags