Limiting results with a join while not counting joined results?

Question

Don't know if this is possible, I am looking a writing social network-esque code but need a bit of help.

SELECT wall.Wid, wall.uid, wall.wcomment, wall.name, comment.wid, comment.comment,comment.wid 
FROM wall 
LEFT JOIN comment ON wall.Wid=comment.wid 
WHERE wall.uid in (SELECT fid from friends where uid = 3) 

Cheers guys

Answer 1

expanding as answer :

it's not actually easy to do in SQL, especially not limiting both wall posts AND comments. you might be better off doing this in a loop, retrieving x wall posts and then for each one retrieving y comments. that way you can also cache the comments for each post and save retrieving them each time.

you could limit just the count of wall posts like this :

SELECT wall.Wid, wall.uid, wall.wcomment, wall.name, comment.wid, comment.comment, comment.wid
FROM wall LEFT JOIN comment ON wall.Wid=comment.wid 
WHERE wall.Wid in (SELECT Wid FROM wall WHERE wall.uid IN (SELECT fid from friends where uid = 3) LIMIT 0, 10)

bear in mind that this is pretty inefficient, since for each comment you'll be retrieving the wall post, so if a wall post has 10 comments you'll get the wall post data 10 times, each time with a different comment. i recommend you do it in a loop with caching.

Answer 2

I'm a scummy MSSQL person, so I'm inferring that somethign likethis would work in MySQL...

SELECT
   wall.Wid,
   wall.uid,
   wall.wcomment,
   wall.name,
   comment.id,
   comment.comment,
   comment.wid
FROM
   wall
LEFT JOIN
   comment
      ON comment.id IN (SELECT id FROM comment WHERE wid = wall.wid ORDER BY id DESC LIMIT 4)
WHERE
   wall.uid IN (SELECT fid from friends where uid = 3)
   AND wall.Wid IN (SELECT Wid FROM wall ORDER BY Wid DESC LIMIT 30)

Note: MSSQL Server is now very good at optimising IN conditions to perform just as well as joins. I have no idea about MySQL's performance in this regard.

EDIT:

If you can't use LIMIT in this manner, then you would seem to be forced to write a query that gets all the comments, but then add some condition to eliminate the ones you do not want. This would seem to involve calulating a "row number" for each comment, which I can only think of doing in one way, and that may not be particularly efficient...

SELECT
   wall.Wid,
   wall.uid,
   wall.wcomment,
   wall.name,
   comment.id,
   comment.comment,
   comment.wid
FROM
   wall
LEFT JOIN
   comment
      ON comment.wid = wall.wid
      AND 4 >= (
               SELECT
                  COUNT(*)
               FROM
                  comment AS [newer_comment]
               WHERE
                  [newer_comment].wid = comment.wid
                  AND [newer_comment].date_added > comment.date_added
               )
WHERE
   wall.uid IN (SELECT fid from friends where uid = 3)
   AND 30 >= (
             SELECT
                COUNT(*)
             FROM
                wall AS [newer_wall]
             WHERE
                [newer_wall].uid IN (SELECT fid from friends where uid = 3)
                AND [newer_wall].date_added > wall.date_added
             )

NOTE: I've added a [date_added] field to both the [wall] and [comment] tables to be used for determining the order by which you're selecting the "first 30" records...

For this to be even remotely efficient it is imperative that indexes are added to make the COUNT(*) sub queries as fast as possible.

There is a final (more complex, but possibly more intelligent and efficient, version I can sumit later today, but I have to get back to work now. If this works for you I'll post that 'better' version)

Answer 3

Here are some ways to do this -How to select TOP N rows from a table for each group?