WordPress MySQL – custom meta key order by key and date

1 Views

I have a meta key which is set by a select drop down so a user can select an option between 1 and 14 and then save their post. I want the posts to display on the page from 1 to 14 ordered by date but if the user creates a new set of posts the next day I also want this to happen so you have posts 1 to 14 each day displaying in that order.. the SQL i have so far is as follows 

    SELECT  SQL_CALC_FOUND_ROWS
            wp_postmeta.meta_key, 
            wp_postmeta.meta_value,
            wp_posts.*
       FROM wp_posts  
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
      WHERE 1=1
        AND wp_posts.post_type = 'projectgallery'
       AND (      wp_posts.post_status = 'publish' 
               OR wp_posts.post_status = 'private')
       AND (wp_postmeta.meta_key = 'gallery_area' )
  GROUP BY wp_posts.post_date asc
  ORDER BY CAST(wp_postmeta.meta_value AS UNSIGNED) DESC,
           DATE(wp_posts.post_date) desc;

Which gives me the following output noticte thatthe posts entered at different dates with either 1 or 3 show up in sequence, ideally i want the latest ones to display directly after 14 so it starts over again. the number 14 should not be static either as if someone adds another option to the select then it will increase and decrease if an option is removed.
enter image description here

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. GROUP BY is confusingly named. It only makes sense when there’s a SUM() or COUNT() or some such function in the SELECT clause. It’s not useful here.

    The canonical way of getting a post_meta.value into a result set of post items is this. You’re close but this makes it easier to read.

    SELECT  SQL_CALC_FOUND_ROWS
        ga.meta_value gallery_area,
        p.*
   FROM wp_posts p  
   LEFT JOIN wp_postmeta ga ON p.ID = ga.post_id AND ga.meta_key = 'gallery_area'
  WHERE 1=1
   AND p.post_status IN ('publish', 'private')
   AND p.post_type = 'projectgallery'

    Notice the two parts of the ON clause in the JOIN. That way of doing the SQL gets you just the meta_key value you want cleanly.

    So, that’s your result set. You’ll get a row for every post. If the metadata is missing, you’ll get a NULL value for gallery_area.

    Then you have to order the result set the way you want. First order by date, then order by gallery_area, like so:

    ORDER BY DATE(p.post_date) DESC, 
      0+gallery_area ASC

    The 0+value trick is sql shorthand for casting the value as an integer.

    Edit. Things can get fouled up if the meta_value items contain extraneous characters like leading spaces. Try diagnosing with these changes. Put 

      DATE(p.post_date) pdate,
  0+ga.meta_value numga,
  ga.meta_value gallery_area

    in your SELECT clause. If some of the numga items come up zero, this is your problem.

    Also try 

     ORDER BY DATE(p.post_date) DESC,
          0+TRIM(gallery_area) ASC

    in an attempt to get rid of the spaces. But they might not be spaces.