Thread

Alright… anyone up to helping me build a query?

I need to grab the 5 latest comments on any channel entries written by a particular author.  The channel entries must have the status of “open”.

Why wouldn’t I just use the {exp:comment:entries} module, you ask? Because it has a bug that won’t allow the entry_status to filter properly.  See here: http://ellislab.com/forums/viewthread/187328/

Hi Jesse,

Totally off the top of my head from what I can remember of the database tables and the respective fields but hopefully this should work. If not then I’ll take another look a little later on as I’m not at a computer at the moment.

SELECT
        name, email, comment_date, comment
FROM
        exp_comments
WHERE
        author_id = '20'
AND
        status = 'open'
LIMIT 5

You’ll need to change the 20 above to the ID of the member you wish to return comments for.

Best wishes,

Mark

Thanks Mark -

Let me clarify a bit. 

I need to select comments from all the entries written by the same author.  So, I would want to get all the comments on entries that I have written… not necessarily that I’m the author of the comments.  The author I’m referring to is the author of the entries, not the author of the comments.

The status is not on the comments, but on the entries that the comments belong to.

So I’m sure this will have to join several tables.  Probably the exp_comments, exp_channel_titles, exp_statuses

Here is what I’ve got so far

SELECT exp_comments.author_id AS `c.author_id`, 
    exp_comments.`comment` AS `c.comment`, 
    exp_comments.`name` AS `c.name`, 
    exp_members.photo_filename AS `c.photo_filename`
FROM exp_comments, exp_members, exp_channel_titles
WHERE exp_comments.channel_id = '4'
AND exp_channel_titles.author_id = '2'
AND exp_channel_titles.status = 'open'

It’s returning a lot of data, however, it’s not very accurate 😊

I can’t dive into it, but I’m guessing you need to join those tables rather than just pull from them. That way the data is connected.

OK, we’ve got it happening now!

SELECT exp_comments.author_id AS `c.author_id`, 
  exp_comments.`comment` AS `c.comment`, 
  exp_comments.`name` AS `c.name`, 
  exp_channel_titles.author_id AS `ct.author_id`,
  exp_channel_titles.channel_id AS `ct.channel_id`, 
  exp_channel_titles.entry_id AS `ct.entry_id`,
  exp_members.photo_filename AS `c.photo_filename`,
  exp_comments.comment_date AS `c.date`                    
FROM exp_channel_titles
JOIN exp_comments  ON exp_comments.entry_id = exp_channel_titles.entry_id
JOIN exp_members ON exp_members.member_id = exp_comments.author_id
WHERE exp_channel_titles.channel_id = 4
AND exp_channel_titles.`status` LIKE '%Open%'
AND exp_channel_titles.author_id = 2
ORDER BY `c.date` DESC
LIMIT 5

As you can see, I’ve hard coded in the exp_channel_titles.author_id… unfortunately I have the author’s username readily available, but not the author_id.  So how to get that in this query…

replace

AND exp_channel_titles.author_id = 2

with

exp_members.username = 'schutte'...

or something like that

Well, I think this works:

SELECT 
    M.username AS `admin_username`,
    M.photo_filename AS `c.photo_filename`, 

    C.`author_id` AS `c.author_id`, 
    C.`comment` AS `c.comment`, 
    C.`name` AS `c.name`, 
    C.comment_date AS `c.date`,

    CH.author_id AS `ct.author_id`, 
    CH.channel_id AS `ct.channel_id`, 
    CH.entry_id AS `ct.entry_id`

FROM exp_members M
INNER JOIN exp_channel_titles CH ON M.member_id = CH.author_id
INNER JOIN exp_comments C ON CH.entry_id = C.entry_id

WHERE M.username = '{segment_3}'
AND CH.channel_id = 4
AND CH.`status` LIKE '%Open%' 

ORDER BY `c.date` DESC
LIMIT 5

Looks good to me!

Only thing I would do different, and it is tiny, but rather than

CH.`status` LIKE '%Open%'

CH.`status` = 'Open'

A bit less processing by the query.

Thanks Jeremy - The reason I’m using LIKE is that sometimes the status is “open” and sometimes it is “Open”.  I have to do some fixing of my SafeCracker forms to get that consistent.  Then I can remove the LIKE…

ahhhh, I see. Then I think you are looking for

CH.`status` LIKE '_pen'

CH.`status` LIKE '\_pen'

CH.`status` LIKE ‘%Open%’ is going to find anything with ‘Open’ in it, and should still be ignoring anything with a lowercase ‘O’.

Or you could make it explicit I suppose

AND CH.`status` LIKE '%Open%'

becomes

AND (CH.`status` = 'Open' OR CH.`status` = 'open')

Alright - thanks for that update! I’ll make the changes…

nice work, Jesse:

you may want to do some benchmarking using:

EXPLAIN SELECT 
    M.username AS `admin_username`,
    M.photo_filename AS `c.photo_filename`, 

    C.`author_id` AS `c.author_id`, 
    C.`comment` AS `c.comment`, 
    C.`name` AS `c.name`, 
    C.comment_date AS `c.date`,

    CH.author_id AS `ct.author_id`, 
    CH.channel_id AS `ct.channel_id`, 
    CH.entry_id AS `ct.entry_id`

FROM exp_members M
INNER JOIN exp_channel_titles CH ON M.member_id = CH.author_id
INNER JOIN exp_comments C ON CH.entry_id = C.entry_id

WHERE M.username = 'random_user_name_here'
AND CH.channel_id = 4
AND (CH.`status` = 'Open' OR CH.`status` = 'open') 

ORDER BY `c.date` DESC
LIMIT 5

If you run:

show create table exp_members;

You’ll notice there isn’t an index on the username, so you *might* get a bit more mileage if you select the member_id from exp_members where username = ‘blah’ first, then use the result of that in the big query.  Make sense?

So you’ve written a hard query, now comes the fun part, tuning it 😉

-greg