Geek asks and answers

Show last post by date from custom taxonomy in WordPress loop

I have a regular taxonomy that I use to group posts in a series (series is the term in this taxonomy). I would like to treat these related posts as special cases when executing a WordPress loop.

Only the first message in the series (the one with the most recent date) should be displayed, and all other messages that do not belong to the term in the user taxonomy should be treated as regular messages. In addition to the taxonomy of custom series, messages can also be tagged or classified (so that no more than three taxonomies, including custom ones). The cycle should contain a fixed number of posts (for example, the number of posts on the first page specified in the WordPress backend).

I don’t understand how to group these messages, partly because all the data is in different tables and because each record can belong to several taxonomies. Of course, looping around the posts array, discarding old messages in the series after calling MySQL, this is possible, but in this case, a fixed number of messages is difficult to maintain without making additional queries to the database.

Therefore, I would like to implement a purely SQL solution. I experimented with posts_clauseshook. This query below returns the message with the highest identifier, not the date, in the series and is most likely a database tax.

$clauses['fields'] .= ", $wpdb->posts.ID AS postID, (SELECT $wpdb->terms.term_id 
   FROM $wpdb->posts, $wpdb->term_taxonomy, $wpdb->term_relationships, $wpdb->terms     
   WHERE $wpdb->posts.ID=postID 
     AND $wpdb->term_taxonomy.taxonomy='post-series' 
     AND $wpdb->term_relationships.term_taxonomy_id=$wpdb->term_taxonomy.term_taxonomy_id 
     AND $wpdb->posts.ID=$wpdb->term_relationships.object_id 
     AND $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id 
   ORDER BY $wpdb->posts.post_date DESC 
   LIMIT 0,1) AS uniqueID";

$clauses['groupby'] = "IFNULL(uniqueID,$wpdb->posts.ID)";

Following the suggestions over WordPress, the following SQL query is created:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.*, wp_posts.ID AS postID, 
  (SELECT wp_terms.term_id FROM wp_posts, wp_term_taxonomy, wp_term_relationships, wp_terms 
    WHERE wp_posts.ID=postID 
      AND wp_term_taxonomy.taxonomy='post-bundles'
      AND wp_term_relationships.term_taxonomy_id=wp_term_taxonomy.term_taxonomy_id 
      AND wp_posts.ID=wp_term_relationships.object_id 
      AND wp_terms.term_id=wp_term_taxonomy.term_id 
    ORDER BY wp_posts.post_date DESC LIMIT 0,1) 
  AS uniqueID FROM wp_posts 
WHERE 1=1
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY IFNULL(uniqueID,wp_posts.ID) 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

I also read this tutorial , but I'm not sure if it is applicable in this case.

, : ( post_date), , . , , .

+5
1

, ( )

aproach , , .

select * from wp_posts where ID in(
select ID from
(
select wp_posts.ID , wp_posts.post_date, d.name from wp_posts as a
join wp_term_relationships as b on ( a.ID = b.object_id)
join wp_term_taxonomy as c on (b.term_taxonomy_id = c.term_taxonomy_id)
join  wp_terms as d on (c.term_id = d.term_id)
where c.taxonomy = 'post-series'
group by d.name 
having (wp_posts.post_date = max(wp_posts.post_date))
)tmp)

i d.name, , , max post_date

, where ID in ()

, , mysql.

+2

More articles:


All Articles