CODEWITHSUNDEEP
mysqlsqlwordpress
sfarzoso
sfarzoso

Reputation: 1600

Cannot order posts by meta value

I'm trying to get a list of Wordpress posts, sorting them using the meta_key = "zoacres_property_price" in DESC order, so I did:

SELECT SQL_CALC_FOUND_ROWS p.* 
                      FROM wpps_posts p
                     CROSS
                      JOIN wpps_postmeta m
                     INNER
                      JOIN wpps_term_relationships r
                        ON p.ID = r.object_id
                     WHERE r.term_taxonomy_id IN (76) 
                       AND p.post_type = 'zoacres-property' 
                       AND p.post_status = 'publish'  
                       AND m.meta_key = 'zoacres_property_price'
                     GROUP 
                        BY p.ID 
                     ORDER 
                        BY m.meta_value ASC 
                    LIMIT 6, 6

unfortunately I got this error:

Unknown column wpps_posts.ID in on clause

what I did wrong? The column exists

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Never use commas in the FROM clause!

I am guessing that you actually want to filter the posts by the conditions in the other two tables. So, I suspect you want LEFT JOIN:

SELECT SQL_CALC_FOUND_ROWS wpps_posts.* 
FROM wpps_posts p JOIN
     wpps_term_relationships tr
     ON p.ID = tr.object_id AND tr.term_taxonomy_id IN ('76') JOIN
     wpps_postmeta pm
     ON p.ID = pm.post_id
WHERE p.post_type = 'zoacres-property' AND
      p.post_status = 'publish' 
      pm.meta_key = 'zoacres_property_price'
ORDER BY pm.meta_value ASC
LIMIT 6, 6

Upvotes: 3

Related Questions