Please help getting my item and comments query to work

[jimmyoneshot]

I thought I had this working recently but am now struggling to do so. I basically need to display a list of items with each item followed by the comments made on that item with the items themselves being displayed newest to oldest and the comment beneath each item being displayed oldest to newest ala Facebook's "wall". I'm trying to do this in 1 query. Here is the query I have so far:-

 

(SELECT

@item:='item' AS row_type,
items.id AS row_item_id,
items.id AS id
items.body AS body
items.date AS date

FROM items

order by date desc)

UNION ALL

(SELECT

@comment:='comment' AS row_type,
items.id AS row_item_id,
item_comments.id AS id
item_comments.body AS body
item_comments.date AS date

LEFT JOIN items
ON (item_comments.id2 = items.id)

FROM item_comments

order by date asc

)

ORDER BY row_item_id desc

 

Are there any changes I can make to the above in order to get it to function the way I described? Perhaps by adding a group by statement somewhere? I've tried several alterations but none seem to work

[jimmyoneshot]

Anyone 

[fenway]

First, those variables aren't doing anything in this query.

 

Second, the order by clauses inside each select aren't doing anything either.

 

Sort at the very end, after the UNION.

[jimmyoneshot]

Hi fenway.

 

Do you mean the row_item_id?

 

If so those are used to affect the front end. I have some php if statements on my page which only print out a row based on what row_item_id is. That is what that is used for.

 

Thanks for the advice so far.

 

How can I sort the query to order it the way I described earlier?

 

 

[fenway]

No, I mean the user variables.

 

And you should be able to order it however you'd like in the final order by clause.

[jimmyoneshot]

Cheers. Yep but that is exactly what I'm struggling with. The order itself. Everything else is working ok.

 

Wherever the order is meant to go within the query I simply need to get it to work so that it orders the result in the right way.

 

I need to use group, order by or whatever else is required to get it so the result set looks like the following but actually doing it is what I'm not sure on:-

 

item 3

first comment on item 3

second comment on item 3

third comment on item 3

fourth comment on item 3

 

item 2

first comment on item 2

second comment on item 2

 

item 1

first comment on item 1

second comment on item 1

third comment on item 1

 

So basically every item ordered so that the newest item is on top and the oldest item is on the bottom and each item is followed by all of the comments on that particular item

 

How can I use my order by statement in order to do this is all I really need to know how to do?

[jimmyoneshot]

I finally managed to get the order by working like this:-

 

(SELECT

@item:='item' AS row_type,
items.id AS row_item_id,
items.id AS id,
items.id AS item_id,
NULL AS comment_id,
items.body AS body,
items.date AS date

FROM items

)

UNION ALL

(SELECT

@comment:='comment' AS row_type,
items.id AS row_item_id,
item_comments.id AS id,
NULL AS item_id,
item_comments.id AS comment_id,
item_comments.body AS body,
item_comments.date AS date,

LEFT JOIN items
ON (item_comments.id2 = items.id)

FROM item_comments

)

ORDER BY row_item_id desc, item_id desc, comment_id asc

 

The above works great for the ordering.

 

I now need to make a tweak to this query though.

 

My problem is users may make a load of comments per item so I have decided if there are less than 4 comments per item then those 4 comments will be shown by default however if there are more than 4 comments on an item then only the very last comment will be shown and above that a link saying "show all <comment count> comments" will appear beneath each item so basically like this:-

 

item 3

first comment on item 3

second comment on item 3

third comment on item 3

 

item 2

Link to View All 10 comments on item 2

last comment on item 2

 

item 1

Link to View All 5 comments on item 1

last comment on item 1

 

What would be the best way to implement this into the above query? I'm thinking I am going to need the first 4 comments and the last comment for each item which leads me to thinking I am going to need some sort of For each loop but is this possible in sql?

 

The other way I'm contemplating is joining all comment details for an item onto an item row i.e. an item row would have all the regular item details and would then have comment1_body, comment1date, comment2_body, comment2_date etc That way if these are null on the front end I could simply not print them out.

 

What way seems best?

[fenway]

That's up to your script in PHP.

[jimmyoneshot]

Yes but say for example each item has 500 comments, when taking into account performance considerations surely I shouldn't be selecting them all in my sql query and should only select a few or is this negligible.

 

How would you go about doing this in sql + php at first glance? Does my query look along the right lines?

[fenway]

You're asking about getting the top N records for a given group -- I've covered that many times before on this board.