Custom queries selecting events by category result in incorrect SQL

  • Resolved Andy Keith

    (@andykeith)


    2 years, 3 months ago

    Hi,

    Tested with The Events Calendar 6.0.5.

    We have a plugin which supports listing events in The Events Calendar. When querying by category (tribe_events_cat) the resulting SQL is incorrect when selecting events in more than one category.

    For example, the following query which should display events from 3 categories:

    Array
(
    [post_type] => tribe_events
    [post_status] => publish
    [tax_query] => Array
        (
            [0] => Array
                (
                    [taxonomy] => tribe_events_cat
                    [terms] => Array
                        (
                            [0] => conferences // ID: 184
                            [1] => meetups // ID: 183
                            [2] => expos // ID: 182
                        )

                    [operator] => IN
                    [field] => slug
                )

        )
    [order] => DESC
    [orderby] => date
    [no_found_rows] => 1
    [posts_per_page] => 10
    [offset] => 0
)

    Will produce this SQL:

    SELECT wp_posts.*
 FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)  LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) JOIN wp_tec_occurrences ON wp_posts.ID = wp_tec_occurrences.post_id
 WHERE 1=1 
 AND ( 
  wp_term_relationships.term_taxonomy_id IN (182,183,184) 
  AND 
  tt1.term_taxonomy_id IN (184)
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_tec_occurrences.occurrence_id
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

    The first check on term_taxonomy_id is correct and built by WordPress from the supplied args, but the second check – tt1.term_taxonomy_id IN (184) – is incorrect as it will limit the results to just the first category.

    • This topic was modified 2 years, 3 months ago by Andy Keith.
Viewing 12 replies - 1 through 12 (of 12 total)
  • Plugin Support Abz

    (@abzlevelup)

    Hi @andykeith, that’s a good catch, and thanks for sharing more information here. I have already reached out to our Devs, and we will investigate further.

    Just curious could you also share the entire syntax on this thread? I’d also like to confirm something regarding the second checker on your example here.

    Looking forward to hearing from you.

    Best,
    Abz

    Thread Starter Andy Keith

    (@andykeith)

    Hi Abz,

    Thanks for your reply. Here’s the code where we build the query and fetch the posts:

    
$query_args = [
    'post_type'        => 'tribe_events',
    'post_status'      => 'publish',
    'tax_query'        => [
         [
             'taxonomy' => 'tribe_events_cat'
             'terms'    => [ 'conferences', 'meetups', 'expos' ]
             'operator' => 'IN'
             'field'    => 'slug'
         ]
    ],
    'meta_query'       => [],
    'order'            => 'DESC',
    'orderby'          => 'date',
    'no_found_rows'    => true,
    'suppress_filters' => false,
    'posts_per_page'   => 10
    'offset'.          => 0
];

$query = new WP_Query( $query_args );
$posts = ! empty( $query->posts ) ? $query->posts : [];

// display $posts as a list...

    I’d also like to confirm something regarding the second checker on your example here

    Sure, what did you want to confirm?

    Plugin Support Abz

    (@abzlevelup)

    Hi @andykeith, thanks for sharing the full query here. Yes, I’d like to confirm if it was related to another issue I was working on. It seems like it is not, and this is a new issue. I’d share this with our Devs to further investigate.

    I’d let you know whenever I hear back from them. Appreciate you bringing this to our attention.

    Hang in there,
    Abz

    Thread Starter Andy Keith

    (@andykeith)

    Thanks @abzlevelup.

    Plugin Support Abz

    (@abzlevelup)

    Hi @andykeith, thanks for your patience. We’ve already identified this as a bug on our plugin and already created an internal bug ticket for this, and I added your case. Thanks for all the details. I will definitely pass it along to our devs.

    I am unable to provide an exact date or time estimate for when this bug will be fixed, but you’ll be notified when there are any updates.

    If you do have other concerns or issues you need help with, please create another ticket, as it helps us track different issues.

    Again, thank you for your patience. Hang in there.

    Best,
    Abz
    ________________________

    Internal Bug Ticket Reference:
    BTRIA-1528

    Thread Starter Andy Keith

    (@andykeith)

    Great, thanks!

    Plugin Support Abz

    (@abzlevelup)

    Hi @andykeith, no worries. I am glad that I could help you with this one.

    Thanks for your patience here. Rest assured that this is already on our radar.

    We’d notify you on this thread whenever there are updates.

    Hang in there,
    Abz
    ________________________

    Internal Bug Ticket Reference:
    BTRIA-1528

    Plugin Support Darian

    (@d0153)

    Hi @andykeith

    Just to followup on this thread.

    Are you still having an issue using the latest version of our plugin (TEC v6.0.13.1)? Looking forward to your reply.

    If you do have other concerns or issues you need help with, please create another support topic as indicated in the www.remarpro.com support guidelines.

    Thread Starter Andy Keith

    (@andykeith)

    Hi @d0153, I’m just waiting to hear back from our development team. Will let you know.

    Plugin Support Darian

    (@d0153)

    Hi @andykeith

    That’s great! Let us know if you have further questions or concerns.

    If you do have other concerns or issues you need help with, please create another support topic as indicated in the?www.remarpro.com?support guidelines.

    Plugin Support Darian

    (@d0153)

    Hi @andykeith

    This thread has been inactive for a while, so we’ll go ahead and mark it Resolved. Please open a new thread if any other questions arise, and we’d be happy to help.

    Thread Starter Andy Keith

    (@andykeith)

    Sorry for the delay. I can confirm that the bug is now fixed in the latest version of TEC.

Viewing 12 replies - 1 through 12 (of 12 total)
  • The topic ‘Custom queries selecting events by category result in incorrect SQL’ is closed to new replies.