possible taxonomy issue with group by

[PodsBot]

possible taxonomy issue with group by submitted via Slack by jimtrue

[kristonovo]

Fetch all events ordered by festival type and then by title

festival pod is a custom taxonomy.
event pod is a custom post type.

// assume we´ve got 6 events and 2 festivals

$params = array('orderby' => 'festival.name, post_title', 'limit' => -1, 'groupby' => 'festival.name');
$events = pods('event', $params)

// output should be
Event 1 (Festival 1)
Event 3 (Festival 1)
Event 5 (Festival 1)
Event 2 (Festival 2)
Event 4 (Festival 2)
Event 6 (Festival 2)

// ... but is
Event 1 (Festival 1)
Event 2 (Festival 2)

After testing a while it seems to work when event gets a pod relationship field targeting festival taxonomy. But only without groupby.

$params = array('orderby' => 'festival.name, post_title', 'limit' => -1);

The downside with this is that the festival relation as a taxonomy couldn´t be maintained anymore via wordpress´ quickedit form (ok, extra plugin could do it)

output is

should be

[sc0ttkclark]

When you group by festival.name means that only one record per festival will be shown.

Sounds like what you really want is to order by year, then festival name, then post title, right?

[jimtrue]

Brought this one over from discussion in Slack #support, we had tested with using Taxonomy and it appeared to be ignoring the orderby taxonomy.name. That's primarily what we're testing as the issue. I'm doing a local test to see if there is an issue with orderby as it relates to associated taxonomy over relationship to taxonomy, because that would be a problem.

Also, making a note to myself that group by should really only be used for summary totals calculations and stuff like that, I'm guessing, if it's only returning one row per group. @sc0ttkclark is this one of those situations where groupby is best paired with 'fields' or 'sql' parameters as well to do proper summary calculations?

[kristonovo]

@sc0ttkclark that´s true. At the beginning I misunderstood the concept of groupby but there is still the behaviour that
$params = array('orderby' => 'festival.name, post_title', 'limit' => -1);
ends up like the output shown in the images. I tried to reproduce this issue with a freshly installed wordpress + pods version. Unfortunately it worked how it should 😀

[jimtrue]

@kristonovo It sorted appropriately? Using just taxonomy like you wanted?

[kristonovo]

@jimtrue within the freshly installed test setup: yes. With my actual project: no. Here I still have to assign a real pod relationship field to achieve the wanted output ... are there any debug steps I could follow to give some more info?

[jimtrue]

@kristonovo That's quite odd. That means something else is 'stepping' on the sort. @sc0ttkclark any suggestions? You could try looking at the malfunctioning sort with the following tacked onto the URL: ?pods_debug_sql_all=1 so we could get an eye on the SQL queries being called and compare them.

Do you have any other plugins running on your malfunctioning version that might be touching taxonomy?

[sc0ttkclark]

@kristonovo what in your screenshot is the "festival.name"?

[jimtrue]

@sc0ttkclark that would be the 2014, 2015

[sc0ttkclark]

Might consider 'orderby' => 'CAST( festival.name AS INT(4) ) DESC, t.post_title' (if you want it 2015, then 2014).

[kristonovo]

I´ll try out with debug url and CAST query soon.

[kristonovo]

@sc0ttkclark
array('orderby' => 'CAST(festivaljahr.name AS INT(4)) DESC, post_title', 'limit' => -1);
produces

[You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(4)) DESC, post_title, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`' at line 20]
        SELECT DISTINCT `t`.* FROM `wp_table_posts` AS `t` LEFT JOIN `wp_table_term_relationships` AS `rel_festival` ON `rel_festival`.`object_id` = `t`.`ID` LEFT JOIN `wp_table_term_taxonomy` AS `rel_tt_festival` ON `rel_tt_festival`.`taxonomy` = 'festival' AND `rel_tt_festival`.`term_taxonomy_id` = `rel_festival`.`term_taxonomy_id` LEFT JOIN `wp_table_terms` AS `festival` ON `festival`.`term_id` = `rel_tt_festival`.`term_id` WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_type` = "event" ) ) ORDER BY CAST(`festival`.`name` AS INT(4)) DESC, post_title, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`

[sc0ttkclark]

Maybe without INT(4) and instead use DECIMAL(4,0)

[kristonovo]

@jimtrue
array('orderby' => 'festivaljahr.name, post_title', 'limit' => -1);
with pods_debug_sql_all=1

1

SELECT

DISTINCT
`t`.*
FROM `wp_table_posts` AS `t`

LEFT JOIN `wp_table_term_relationships` AS `rel_festival` ON
`rel_festival`.`object_id` = `t`.`ID`

LEFT JOIN `wp_table_term_taxonomy` AS `rel_tt_festival` ON
`rel_tt_festival`.`taxonomy` = 'festival'
AND `rel_tt_festival`.`term_taxonomy_id` = `rel_festival`.`term_taxonomy_id`

LEFT JOIN `wp_table_terms` AS `festival` ON
`festival`.`term_id` = `rel_tt_festival`.`term_id`

WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_type` = "event" ) )


ORDER BY `festival`.`name`, post_title, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`

2

SELECT

DISTINCT
`t`.*
FROM `wp_table_posts` AS `t`

LEFT JOIN `wp_table_term_relationships` AS `rel_festival` ON
`rel_festival`.`object_id` = `t`.`ID`

LEFT JOIN `wp_table_term_taxonomy` AS `rel_tt_festival` ON
`rel_tt_festival`.`taxonomy` = 'festival'
AND `rel_tt_festival`.`term_taxonomy_id` = `rel_festival`.`term_taxonomy_id`

LEFT JOIN `wp_table_terms` AS `festival` ON
`festival`.`term_id` = `rel_tt_festival`.`term_id`

WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_type` = "event" ) )


ORDER BY `festival`.`name`, post_title, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`

3

`SELECT `ID`, `post_name`, `post_parent` FROM `wp_table_posts` WHERE `post_type` = '_pods_field' AND ( `post_name` IN ( 'festival', 'name' ) )`

4

SELECT

DISTINCT
*, `t`.`term_id` AS `pod_item_id`
FROM `wp_table_terms` AS `t`
LEFT JOIN `wp_table_term_taxonomy` AS `tt` ON `tt`.`term_id` = `t`.`term_id`
LEFT JOIN `wp_table_term_relationships` AS `tr` ON `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id`
WHERE ( ( `t`.`term_id` = 67 ) AND ( `tt`.`taxonomy` = "festival" ) AND ( `tt`.`taxonomy` = "festival" ) )


ORDER BY `t`.`name`, `t`.`term_id`

5

SELECT

DISTINCT
*, `t`.`term_id` AS `pod_item_id`
FROM `wp_table_terms` AS `t`
LEFT JOIN `wp_table_term_taxonomy` AS `tt` ON `tt`.`term_id` = `t`.`term_id`
LEFT JOIN `wp_table_term_relationships` AS `tr` ON `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id`
WHERE ( ( `t`.`term_id` = 67 ) AND ( `tt`.`taxonomy` = "festival" ) AND ( `tt`.`taxonomy` = "festival" ) )


ORDER BY `t`.`name`, `t`.`term_id`

6

SELECT

DISTINCT
t.post_title AS aktuelles_title, t.post_date AS aktuelles_date, t.post_excerpt AS aktuelles_excerpt, t.ID AS aktuelles_id
FROM `wp_table_posts` AS `t`

WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_type` = "aktuelles" ) )


ORDER BY t.post_date DESC, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`
LIMIT 0, 3

7

SELECT

DISTINCT
t.post_title AS aktuelles_title, t.post_date AS aktuelles_date, t.post_excerpt AS aktuelles_excerpt, t.ID AS aktuelles_id
FROM `wp_table_posts` AS `t`

WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_type` = "aktuelles" ) )


ORDER BY t.post_date DESC, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`
LIMIT 0, 3

[kristonovo]

@sc0ttkclark
no more sql error with DECIMAL(4,0) but again ordered by event not by festival
In the meantime all plugins except pods are disabled. Still having that issue :(

[kristonovo]

Testing only with festival.name:

$params = array('orderby' => 'festival.name DESC');

outputs

while
$params = array('orderby' => 'festival.name ASC');

outputs

[sc0ttkclark]

Are you entirely sure you've provided the correct information about the output? Can you provide the PHP you're using to list the content?

[kristonovo]

The longer it takes to find a solution the more I worry it might be my own fault 💃

<?php
/**
 * Template Name: Events
 */

get_header();
?>

<div class="column-1-3">
    <h1><a href="/events" target="_self">Events</a><br><br></h1>

    <div class="event">

        <?php
        $params = array('orderby' => 'festival.name');
        $events = pods('event')->find($params);

        if ($events->data()) {

            while ($events->fetch()) {

                echo $events->display('festival.name') . ' - ' . $events->display('post_title') . '<br>';


            }

        }
?>

    </div>

</div>


<?php get_sidebar( 'right' ); ?>
<?php get_footer(); ?>

... and this is the setting in event pod:

[sc0ttkclark]

Any chance you'd be willing to provide a login for us to get into your Pods Admin area to look into this for you? You can send it privately on our Slack (http://pods.io/chat/) or through this form: https://pods.io/help/

[kristonovo]

Yes, I could prepare a dev environment. Would need some time though. In the meantime I tested one other thing:

• creating a test pod called bio (custom post type)
• checked built-in taxonomy festival
• orderby query works like excepted
• added a second built-in taxonomy called agegroup (like it´s in event pod)
• orderby query no more working
• removed taxonomy relation from bio to agegroup
• still disordered

params always the same:
$params = array('orderby' => 'festival.name DESC');

Might it be possible that multiple taxonomy relations are interfering with orderby? They are kept in database as far as I know. Even if I remove the relation.

[sc0ttkclark]

Following up on this, it wasn't an issue with Pods as much as it was an issue with MySQL.

For whatever reason, when you did an ORDER BY a joined field, it wouldn't order the results correctly. If you added 'where' => 'festival.name IS NOT NULL' then it started to order correctly.

Further digging will be required by a MySQL expert to determine why this happens and how we can mitigate it in the future.