Query to get every item and it's top 3 comments

[jimmyoneshot]

I've been trying to do this for weeks and I know it's been done in other ways before but I can't seem to apply it to my code. Basically I want a single query which retrieves all of the items from my posts table and the top 3 comments for each of those posts. This is a simplified version of my tables but basically they are the following:-

 

posts

 

id

text

date

 

comments

 

id

text

date

 

posts_comments (used to create the relationship between posts and comments)

 

id1 (id of the post the comment is on)

id2 (id of the comment)

 

Can anyone please help out with this?

[fenway]

You want this.

[The Little Guy]

Why do you have 3 tables? You only need 3 tables for a "Many" to "Many" relationship. From what I am understanding, is you have a "One" to "Many" relationship (1 post with many comments).

[jimmyoneshot]

@fenway - Thanks as always  I'll take a look at that now and see if I can get it applied to my example.

 

@The Little Guy - Good question. I need to change this I think. I'm obsessed with using join tables just "incase" I ever need to alter it to many to many at some stage.

 

EDIT - Actually yep sorry about that I posted in the wrong table structure. Ignore that posts_comments table. My postsComments table is actually this:-

 

postsComments

 

id

id2 (id of the post)

text

date

 

[jimmyoneshot]

Grrr. Still can't figure out how to apply this to my code. Can anybody help? 

[fenway]

Grrr. Still can't figure out how to apply this to my code. Can anybody help? 

That link I gave you is exactly what you need to do -- show me what you have so far.

[jimmyoneshot]

As a novice in sql I'm completely lost to be honest.  I don't really understand the 'grouper' part of it. I tried the following as a start but can't get it to work:-

 

SELECT  posts.id AS post_id, posts.text AS post_text, posts.date AS post_date, postsComments.id AS comment_id, postsComments.text AS comment_text, postsComments.date AS comment_date
FROM posts postsComments
WHERE postsComments.grouper = @grouper
ORDER BY
postsComments.grouper, postsComments.id
LIMIT 3, 1

[fenway]

"grouper" is the name of the column you want to group on.

[jimmyoneshot]

I thought that may have been the case and tried the following:-

 

SELECT  posts.id AS post_id, posts.text AS post_text, posts.date AS post_date, postsComments.id AS comment_id, postsComments.text AS comment_text, postsComments.date AS comment_date
FROM posts postsComments
WHERE postsComments.grouper = posts.id
ORDER BY
postsComments.grouper, postsComments.id
LIMIT 3, 1

 

which resulted in the error - "Unknown column 'posts.id' in 'field list'"

[fenway]

Your query needs to look something like this:

SELECT  l.*
FROM    (
        SELECT  grouper,
                COALESCE(
                (
                SELECT  id
                FROM    t_limiter li
                WHERE   li.grouper = dlo.grouper
                ORDER BY
                        li.grouper, li.id
                LIMIT 14, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT grouper
                FROM    t_limiter dl
                ) dlo
        ) lo, t_limiter l
WHERE   l.grouper >= lo.grouper
        AND l.grouper <= lo.grouper
        AND l.id <= lo.mid

where "grouper" is your post ID.

[jimmyoneshot]

Right Ok. I'll have another try at it as soon as I get a chance and will let you know if I can get it working. Thanks