WordPress.org

1. rbernabe
   Member
   Posted 1 year 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

2. rbernabe
   Member
   Posted 1 year 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

Topic Closed

This topic has been closed to new replies.