WordPress.org

1. LatvjuAvs
   Member
   Posted 5 months ago #

   Hello.
   I need help from anyone who understands MYSQL queries well.

   I have 5k posts, each post have category assigned.
   Each category have have order - "wp_terms" have additional field "menu_order", using plugin: "Term Menu Order".
   Each post is tagged also, by one tag, no more, no less.

   I want to list Posts and group them by Category order.
   Also I want to use pagination, i will use offset and limit for that.
   I conjured a MYSQL query, taken out from wordpress, that is not doing the job right.

   SELECT wp_posts.*, wp_terms.term_order
   FROM wp_posts 
   
   INNER
   JOIN wp_term_relationships
   ON (wp_posts.ID = wp_term_relationships.object_id) 
   
   INNER
   JOIN wp_term_relationships AS tt1
   ON (wp_posts.ID = tt1.object_id) 
   
   INNER
   JOIN wp_terms
   ON (wp_terms.term_id = wp_term_relationships.term_taxonomy_id) 
   
   WHERE 1=1
   AND (
   wp_term_relationships.term_taxonomy_id IN (36,38,40,42,44,46) //cat, single or multiple
   AND tt1.term_taxonomy_id IN (20) //tag, single or multiple
   )
   AND wp_posts.post_type = 'post'
   AND (
   wp_posts.post_status = 'publish'
   OR wp_posts.post_status = 'private'
   ) 
   
   ORDER BY wp_terms.term_order ASC //order by category order.
   LIMIT 0, 10

   I hope I made some-clear picture of what I want to achieve, thanks :)

2. LatvjuAvs
   Member
   Posted 5 months ago #

   Continuing this crusade.
   If i use simple query of

   SELECT * from wp_posts
   
   LEFT
   JOIN wp_term_relationships
   ON ( wp_term_relationships.object_id = wp_posts.ID )
   
   LEFT
   JOIN wp_terms
   ON ( wp_terms.term_id = wp_term_relationships.term_taxonomy_id )
   
   WHERE 1=1
   AND wp_posts.post_type = 'post'
   AND (
   wp_posts.post_status = 'publish'
   OR wp_posts.post_status = 'private'
   )
   LIMIT 0, 20

   Result is a posts with 2 relationships with TERMS table.

   POST - 1
   ID: 10
   TERM_ORDER: 1
   TERM_NAME: FIRE //category

   POST - 2
   ID: 10
   TERM_ORDER: 0
   TERM_NAME: blue //tag

   I need TERM_ORDER > 0 so I can order POSTS

   IF I add CATEGORY or TAG criteria: AND $wpdb->terms.term_id = 170
   Then I get either POST with TERM_ORDER: 0 if tag, or number if category.

   I need to select POSTS, filter out by category or/and tag, then get TERM_ORDER from category attached to POST.

   Crazy.

   Will crack this, any input would be awesome :)

3. LatvjuAvs
   Member
   Posted 5 months ago #

   So, after crazy coding I managed to get what I wanted.
   Do not hold your breath as this probably is ugly and slow, but works.

   First, I create view where all products are collected.

   $qqquery = "
   CREATE VIEW samsam AS
   SELECT
   ID, post_content, post_title, post_excerpt, post_status, post_name, guid, post_type, $wpdb->term_relationships.term_taxonomy_id as cat_term_taxonomy_id, tt1.term_taxonomy_id as tag_term_taxonomy_id
   
   FROM $wpdb->posts
   
   LEFT
   JOIN $wpdb->term_relationships
   ON ( $wpdb->term_relationships.object_id = $wpdb->posts.ID )
   
   LEFT
   JOIN $wpdb->term_relationships AS tt1
   ON ( tt1.object_id = $wpdb->posts.ID ) 
   
   WHERE 1=1
   AND $wpdb->posts.post_type = 'post'
   AND (
   $wpdb->posts.post_status = 'publish'
   OR $wpdb->posts.post_status = 'private'
   )
   AND (						wp_term_relationships.term_taxonomy_id IN ( $query_cat ) // ID's of categories, seperated by coma
   AND tt1.term_taxonomy_id IN (' . $add_query_tag_id . ') //ID's of POSTS seperated by coma
   )
   ORDER BY $wpdb->term_relationships.term_taxonomy_id DESC
   ";
   $wpdb->query($qqquery);

   After that I do query where I select view and join relationships and terms to get category order by filtering out order > 0, then group BY id to filter out duplicates.

   $querystr = "
   SELECT * from samsam
   
   LEFT
   JOIN $wpdb->term_relationships
   ON (samsam.ID = $wpdb->term_relationships.object_id) 
   
   LEFT
   JOIN $wpdb->terms
   ON ($wpdb->terms.term_id = $wpdb->term_relationships.term_taxonomy_id)
   AND ($wpdb->terms.menu_order > 0)
   
   WHERE menu_order > 0
   
   GROUP BY ID
   
   ORDER BY menu_order ASC
   
   LIMIT $item_limit_offset, $item_limit_pp"; // first is calculation of products to skip, second is how many posts per page
   
   $pageposts = $wpdb->get_results($querystr, OBJECT);

Reply

You must log in to post.