Order categories by custom field

  • rbernabe

    (@rbernabe)


    17 years, 4 months ago

    Hi All

    Im trying to modify the sort (ORDER BY) behavior for a category based on a custom field that each post contains. Each post has a custom field called ‘lastname’, and this is what I want to sort on. However this data is stored in the wp_postmeta table, and I assume I’d need to modify the usual database query and JOIN this table to be able to sort on it. This is beyond my knowledge…

    Can anyone help?

    Thanks!
    Rob

Viewing 1 replies (of 1 total)
  • Thread Starter rbernabe

    (@rbernabe)

    17 years, 4 months ago

    Update: I’ve gotten in touch with a friend of mine who knows his SQL pretty well. I turned on debugging on my MySQL server and gave him the query that was generated when displaying a specific category, as well as some table schemas. He gave me the following query:

    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)

	LEFT OUTER JOIN wp_postmeta
		ON (wp_posts.ID = wp_postmeta.post_id AND
			wp_postmeta.meta_key = 'lastname')

WHERE 1=1
	AND  wp_term_taxonomy.taxonomy = 'category'
	AND wp_term_taxonomy.term_id IN  ('4')
	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 COALESCE(wp_postmeta.meta_value, post_title) ASC, post_title ASC

LIMIT 0, 10;

    This seems to work fine. The question now is, where do I put this query so it gets run for the categories I want it to?

    Thanks!
    Rob

Viewing 1 replies (of 1 total)

The topic ‘Order categories by custom field’ is closed to new replies.