Weird issue with orber by, inner join in wordpress query

Question

I am working on a custom plugin in wordpress. I have a weird issue with one sql.

SQL:

SELECT SQL_CALC_FOUND_ROWS wp_posts. *
FROM wp_posts
INNER JOIN wp_term_relationships 
ON ( wp_posts.ID = wp_term_relationships.object_id )
INNER JOIN wp_term_taxonomy 
ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id )
WHERE 1 =1
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN ('23')
AND (
wp_posts.post_author =1
)
AND wp_posts.post_type = 'post'
AND (
wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'
)
GROUP BY wp_posts.ID
ORDER BY `wp_posts`.`as_stats_rating` DESC
LIMIT 0 , 30

Its returning the correct data but its not sorting results according to as_stats_rating. I am stumped. Does anyone know what I am doing wrong?

Edit 1 : Update

Here is the structure of wp_posts: alt text

Sample result:

ID  as_stats_rating
1221   8
1222   10

All fields in sample results are :
ID post_author post_date post_date_gmt post_content post_title post_excerpt post_status comment_status ping_status post_password post_name to_ping pinged post_modified post_modified_gmt post_content_filtered post_parent guid menu_order post_type post_mime_type comment_count as_stats_numviews as_stats_numvotes as_stats_votestotal as_stats_rating

By the way, its not only about 'order by wp_posts.as_stats_rating', 'order by wp_posts.as_stats_numviews' have the same issue. (just to clear up, if you are wondering about as_stats_rating having varchar type)

i would suspect a conflict with the limit statement. as a check, take the limit off and see if the order by is correct. — Randy
– Randy, Commented Dec 11, 2010 at 14:38
@Randy : No. Just checked by removing limit clause, the issue persist. — Krishna Kant Sharma
– Krishna Kant Sharma, Commented Dec 11, 2010 at 14:45

Riedsio · Accepted Answer · 2010-12-11 16:36:55Z

1

Any chance that the as_stats_rating field is a string (char/text) datatype and not a numeric (float/int/decimal) datatype?

answered Dec 11, 2010 at 16:36

Riedsio

9,9661 gold badge27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krishna Kant Sharma Over a year ago

Hell. I am so sorry about this questions. As it turns out changing datatype to int solved the issue. Coming to think of it, it was sorting 8 > 10 as texts.

Collectives™ on Stack Overflow

Weird issue with orber by, inner join in wordpress query

Edit 1 : Update

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit 1 : Update

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related