Reputation: 5825
I have a query such that I need to get
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!
Upvotes: 1
Views: 1019
Reputation: 10003
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. :)
Upvotes: 3