csr csr - 4 months ago 8
SQL Question

order post according to custom field position

I want a list of posts according to a custom field in Wordpress.

Here I have 9 posts with 3 different meta values (middle, Left Top, Left bottom) and meta key is "position"

here is posts table

Wp_posts:


Post ID title Description
1 Post1 -----
2 Post2 -----
3 Post3 -----
4 Post4 ---
5 Post5 ----
6 Post6 --
7 Post7 ----
8 Post8 ---
9 Post9 ----
10 Post10 ---


And here is wp_postmeta table:


meta_id post_id Meta_key meta_value
1 1 position left Top
2 2 position Left Bottom
3 3 position Left Top
4 4 position Left Bottom
5 5 position Middle
6 6 position Left Bottom
7 7 position Left Top
8 8 position Left Bottom
9 9 position Left top
10 10 position Middle


Now I want to get a list according to sorting of position (Middle, Left Bottom, Left Top)

Like this one


post_id title meta_value met_key
5 Post5 Middle postion
2 Post2 Left Bottom postion
1 Post1 Left Top postion
10 Post10 Middle postion
4 Post4 Left Bottom postion
3 Post3 Left Top postion
6 Post6 Left Bottom postion
7 Post7 Left Top postion
8 Post8 Left Bottom postion
9 Post9 Left Top postion


I am not expert in sql query.

Answer

On base of this answer

SELECT post_id, title, meta_value, Meta_key
FROM (
   SELECT post_id, meta_value, Meta_key,
          @grp := IF(@pos = meta_value, @grp + 1,
                     IF(@pos := meta_value, 1, 1)) AS grp
   FROM wp_postmeta
   CROSS JOIN (SELECT @grp := 0, @pos := '') AS vars
   WHERE Meta_key = "position"
   ORDER BY meta_value) AS t
LEFT JOIN Wp_posts 
   ON t.post_id =  Wp_posts.PostID
ORDER BY grp, FIELD(meta_value, 'Middle', 'Left Bottom', 'left Top')   

demo