Order By meta_value works in SQL but not on page?

  • Resolved andygk

    (@andygk)


    1 year, 3 months ago

    The shortcode I’m using is:

    [mla_gallery category_name=parking meta_key="incident_date_time" orderby=meta_value order=DESC mla_alt_shortcode=justified_image_grid]

    The SQL query returned with debug=true:

    4087 mla_debug request = ' SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (24) ) AND (wp_posts.post_mime_type LIKE \'image/%\') AND ( wp_postmeta.meta_key = \'incident_date_time\' ) AND wp_posts.post_type = \'attachment\' AND ((wp_posts.post_status = \'inherit\')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC '

    The contents of the db when I manually run that SQL (NB edited to remove the “\” characters) – the meta_data with meta_key value = “incident_date_time” is a plain numeric version of the “post_excerpt” content e.g. 202311161645 and as can be seen, the list is sorted on that date and time as I wanted:

    But the displayed page is not shown in that order…

    Thanks for any help you can provide ??

    The page I need help with: [log in to see the link]

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter andygk

    (@andygk)

    PS fwiw I have turned the cache off for the page in production.

    Plugin Author David Lingren

    (@dglingren)

    Thanks for your report, for posting the shortcode text and for the link to your page; very helpful. Your shortcode includes an mla_alt_shortcode=justified_image_grid parameter. In this case, MLA performs the SQL query that you’ve seen, builds a list of the item ID values and then passes that to JIG as an ids= parameter. JIG then does its own database query to retrieve the items for display. For example, here is a JIG query from my test post:

    SELECT mladev_posts.ID
FROM mladev_posts
WHERE 1=1
AND mladev_posts.ID IN (6410,6412,5906,5904,5905,5625,5218,5227,5230,5231,5233,3974,3648,3649,3510,3511,3512,3515,3516,3381,2942,2570,2650)
AND (mladev_posts.post_mime_type LIKE 'image/%'
OR mladev_posts.post_mime_type = 'font/svg'
OR mladev_posts.post_mime_type = 'image/svg+xml'
OR mladev_posts.post_mime_type = 'application/pdf')
AND mladev_posts.post_type = 'attachment'
AND ((mladev_posts.post_status <> 'trash'
AND mladev_posts.post_status <> 'auto-draft'))
ORDER BY FIELD(mladev_posts.ID,6410,6412,5906,5904,5905,5625,5218,5227,5230,5231,5233,3974,3648,3649,3510,3511,3512,3515,3516,3381,2942,2570,2650)
LIMIT 0, 23

    This second query is working fine on my system and displaying the items in the correct order, but perhaps it is working differently on your system. Have you tried displaying the gallery using just the [mla_gallery] shortcode, e.g.:

    [mla_gallery category_name=parking meta_key="incident_date_time" orderby=meta_value order=DESC mla_caption="{+template:({+attachment_ID+}: {+custom:incident_date_time+})+}"]

    The captions should give you an accurate look at how the gallery is ordered, and you can compare the thumbnail images to see if the order is different from the JIG gallery. If that’s true we can investigate further. I regret that I don’t yet have an answer, but any additional information you can provide will be helpful.

    By the way, I use the excellent Query Monitor – WordPress plugin | www.ads-software.com plugin to capture and analyze the SQL queries.

    Thread Starter andygk

    (@andygk)

    Thanks David, I changed the page in my dev environment replacing the JIG shortcode with the one you supplied (and deactivated the JIG plugin) – and the thumbnails were ordered correctly:

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Order By meta_value works in SQL but not on page?’ is closed to new replies.