Cfrgtkky

We managed awhile back to cobble some really complex (to us) database queries that pull some dynamic meta_values in with the general Post info in WordPress so that the result was a nice, full list of key=>value pairs. It's worked great for years but now we've switched to MariaDB and it's no longer ordering properly and I'm not sure why. See below for one of the big, long examples or screenshot at https://screencast.com/t/mFILYpWf for a bit more formatting.

Any ideas on how I can get it to order by menu_order first, last_name then first_name?

Thanks as always!!

SELECT *

FROM

(

    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,

        wp_posts.post_name,

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'

                 THEN wp_postmeta.meta_value END ) AS 'job_title',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'

                 THEN wp_postmeta.meta_value END ) AS 'prefix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'

                 THEN wp_postmeta.meta_value END ) AS 'first_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'

                 THEN wp_postmeta.meta_value END ) AS 'middle_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'

                 THEN wp_postmeta.meta_value END ) AS 'last_name',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'

                 THEN wp_postmeta.meta_value END ) AS 'suffix',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'

                 THEN wp_postmeta.meta_value END ) AS 'languages',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'

                 THEN wp_postmeta.meta_value END ) AS 'email',

        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'

                 THEN wp_postmeta.meta_value END ) AS 'phone',

        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'

    GROUP BY wp_posts.ID

    ORDER BY menu_order asc, last_name asc, first_name asc

) AS t

WHERE 1=1

Simplify it by getting rid of the outer SELECT!

The reason is that a 'derived' table (your subquery) has no order. (This has long been in Standard SQL, but only recently have MariaDB, and later MySQL, taken advantage of it.) That is, the execution is justified to get rid of your carefully written ORDER BY.

But, by stopping with the inner query; it is obligated to honor the ORDER BY.

MariaDB may have a different mode set. Try this, and see here:

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

Also this may be a duplicate, see this related question.