Complex SQL query, one to many relationship

Question

Hey SO,

I have a query such that I need to get

1. A specific dog
2. All comments relating to that dog
3. The user who posted each comment
4. All links to images of the dog
5. the user who posted each link

I've tried a several things, and can't figure out quite how to work it. Here's what I have (condensed so you don't have to wade through it all):

 SELECT s.dog_id,
        s.name,
        c.comment,
        c.date_added AS comment_date_added,
        u.username AS comment_username,
        u.user_id AS comment_user_id,
        l.link AS link,
        l.date_added AS link_date_added,
        u2.username AS link_username,
        u2.user_id AS link_user_id
 FROM dogs AS d
 LEFT JOIN comments AS c
 ON c.dog_id = d.dog_id
 LEFT JOIN users AS u
 ON c.user_id = u.user_id
 LEFT JOIN links AS l
 ON l.dog_id = d.dog_id
 LEFT JOIN users AS u2
 ON l.user_id = u2.user_id
 WHERE d.dog_id = '1'

It's sorta close to working, but it'll only return me the first comment, and the first link all as one big array with all the info i requested. The are multiple comments and links per dog, so I need it to give me all the comments and all the links. Ideally it'd return an object with dog_id, name, comments(an array of the comments), links(an array of the links) and then comments would have a bunch of comments, date_added, username, and user_id and links would have a bunch of links with link, date_added, username and user_id. It's got to work even if there are no links or comments.

I learned the basics of mySQL somewhat recently, but this is pretty far over my head. Any help would be wonderful. Thanks!

Answer 1

you can't query this in 1 sql query without a lot of redundant data being sent back. because comments and links bear no relation to each other, you'd be best off doing those calls separately.

so 3 calls in total : 1 to retrieve the dog data, 1 to retrieve comments/user, and 1 to retrieve links/user.

SELECT d.dog_id, d.name FROM dogs AS d WHERE d.dog_id = '1';

SELECT c.comment, c.date_added AS comment_date_added,
       u.username AS comment_username, u.user_id AS comment_user_id
       FROM comments AS c LEFT JOIN users AS u ON c.user_id = u.user_id
       WHERE c.dog_id = '1';

SELECT l.link AS link, l.date_added AS link_date_added,
    u.username AS link_username, u.user_id AS link_user_id
       FROM links AS l LEFT JOIN users AS u ON l.user_id = u.user_id
       WHERE l.dog_id = '1';

the reason for this is that although you can join the dogs/comments table well enough, when you start trying to join in the links - how do you relate a link to a comment? there's no obvious join so your only way to do it is to use an OUTER JOIN. this will lead to a lot of redundant data in your calls.

also, this kind of thing makes caching a lot easier and more intuitive. in my experience it's usually easiest to go with a couple of natural queries than a spaghetti join. :)