WordPress.org

1. Zaphod
   Member
   Posted 2 years ago #

   Hi,

   I've a query as follows:

   SELECT wposts .  *
   FROM cc_posts wposts
   LEFT JOIN cc_postmeta cc_make ON ( wposts.ID = cc_make.post_id )
   LEFT JOIN cc_postmeta cc_model ON ( wposts.ID = cc_model.post_id )
   LEFT JOIN cc_postmeta cc_year ON ( wposts.ID = cc_year.post_id )
   LEFT JOIN cc_postmeta cc_price ON ( wposts.ID = cc_price.post_id )
   LEFT JOIN cc_postmeta cc_location ON ( wposts.ID = cc_location.post_id )
   WHERE 1 =1
   AND (
   cc_make.meta_key = 'make'
   AND cc_make.meta_value = 'Ford'
   )
   AND (
   cc_model.meta_key = 'model'
   AND cc_model.meta_value = 'Fiesta'
   )
   AND (
   cc_year.meta_key = 'year'
   AND cc_year.meta_value >1949
   )
   AND (
   cc_price.meta_key = 'price'
   AND cc_price.meta_value >0
   )
   AND (
   cc_location.meta_key = 'location'
   AND cc_location.meta_value = 'Dublin'
   )
   AND wposts.post_status = 'publish'
   AND wposts.post_type = 'post'
   AND wposts.post_date < NOW( )
   ORDER BY cc_price.meta_value DESC
   LIMIT 0 , 10

   The issue is that the price field (see order by - second last line) is stored as text data and I need the query to parse / run it as numeric / int data.

   I've been trying various versions of the CAST and CONVERT but haven't quite got there yet. Any help enormously appreciated.

2. vtxyzzy
   Member
   Posted 2 years ago #

   Try this: ORDER BY cc_price.meta_value+0 DESC

3. Zaphod
   Member
   Posted 2 years ago #

   Dear vtxyzzy (!),

   You are a genius. So simple, and just works. I guess the addition forces SQL to convert to an int so that the addition can occur.

   Thank you so much for this.

   -ZB

Topic Closed

This topic has been closed to new replies.