[wp-trac] Re: [WordPress Trac] #8022: non-DISTINCT query in get_archives for postbypost

[WordPress Trac]

#8022: non-DISTINCT query in get_archives for postbypost
----------------------------------------+-----------------------------------
 Reporter:  kevinB                      |        Owner:  anonymous
     Type:  defect (bug)                |       Status:  reopened 
 Priority:  normal                      |    Milestone:  2.8      
Component:  Template                    |      Version:  2.7      
 Severity:  normal                      |   Resolution:           
 Keywords:  archives,postbypost,filter  |  
----------------------------------------+-----------------------------------
Comment (by mrmist):

 Hi.  I see what you are getting at and since it would be dynamic whether
 to use the distinct or not I suppose it does not harm in the end.

 Perhaps you can given an example of a query the DISTINCT helps with though
 as I am having trouble seeing what you are wanting to get out of the
 query.

 For example this -

 SELECT w.* FROM wp_posts w left join wp_comments c on w.ID =
 c.comment_post_ID;

 Returns duplicates, as you would expect.

 This  -

 SELECT DISTINCT w.* FROM wp_posts w left join wp_comments c on w.ID =
 c.comment_post_ID;

 Returns one row for every row in wp_posts, as you would expect.  However,
 it's completely useless for listing comments.  For that you need (at
 least)

 SELECT DISTINCT w.*, comment_content FROM wp_posts w left join wp_comments
 c on w.ID = c.comment_post_ID;

 And (assuming that you're not wanting to skip duplicate comment content)
 that's just the same as

 SELECT w.*, comment_content FROM wp_posts w left join wp_comments c on
 w.ID = c.comment_post_ID;

 Which will give you the same number of rows as

 SELECT  * FROM wp_posts w left join wp_comments c on w.ID =
 c.comment_post_ID;

 I'm not deliberately trying to be awkward, I'm just trying to ascertain
 what you are using the DISTINCT for, and whether there is some other way
 of doing what you are trying to do.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/8022#comment:8>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software