Multipart queries?

[eluser]rich.a.coy[/eluser]
Hello,

My terminology is probably not correct but could someone please point me toward an explanation on how to run multipart queries?

What I mean is I need to search a table for all the friends of a user then search another table for all those people's posts and return them in reverse chronological order.

I played around with JOIN's today and that does not seem to be the direction I should go.

I need this to be pretty optimized since this will probably be the query run most often on the entire site.

Thanks for any direction you can offer. I searched Google but because I'm not sure what to call this I did not find much that was helpful.

Rich

[eluser]JanDoToDo[/eluser]
You probably do want joins or left joins. Left joins are different because they will return the second table data as empty if there is no entry in the db for that row whereas join will not. E.G:

SELECT * FROM user_friends AS uf WHERE uf.user_id = 1 LEFT JOIN user_posts AS up ON uf.user_id = up.user_id ORDER BY up.date DESC;

This will select everything from the friends table where the user id is 1 and join onto it all of the posts where the user id is also one and then order. If you just did JOIN instead of left join, if a user had no posts then in the returned array, the fields in the second table would not exist, whereas with left join they do but are just empty. As such code will work when manipulating the array as fields will always be there even if they are empty. IS this what you need?

[eluser]JanDoToDo[/eluser]
Sorry, you might need to put the WHERE clause AFTER the left join clause and before the order by clause!

[eluser]rich.a.coy[/eluser]
Thanks for the detailed post. Yes, I think that will get me in the right direction. This is my first project in CI, and MVC, and I'm beginning to think I've bitten off more than I can handle.

[eluser]JanDoToDo[/eluser]
Nooo - Don't be crazy! If you try and do something difficult (Not that this is) you will learn A LOT faster! And there is always help when you need it!

[eluser]rich.a.coy[/eluser]
Thanks for your encouraging words. I have learned a lot in the past month by working on this project.

The code below is working and getting me 95% of where I need to be however I have complicated things a little more by having friendship levels. For example "friends" are a subset of "acquaintances".

I have a field in the friendships table called "level" and a field in the posts table called "viewable" that I need to compare. So somehow I need to add something like the following to the query:

Code:

AND if friendship.level = "f" or if friendship.level = "a" AND post.viewable = "a"

I have tried to add that after the WHERE clause below but I get a lovely SQL syntax error.

Here is what I have so far in my model:

Code:

SELECT * FROM (friendships LEFT JOIN user_profiles on friendships.member2 = user_profiles.user_id)
LEFT JOIN posts ON friendships.member2 = posts.user_id  
WHERE friendships.member1 = '1'
ORDER BY posts.date DESC

I know I could do a comparison in the view and just not display the posts they are not supposed to see but my result set would then be bloated and not accurate.

Thanks again for your help.

Rich

[eluser]crwtrue[/eluser]
try adding these lines after WHERE friendships.member1 = '1'

AND WHERE friendship.level IN ('f', 'a')
AND WHERE post.viewable = 'a'

[eluser]rich.a.coy[/eluser]
Hmmm... This is close I think but post.viewable only needs to be 'a' if friendship.level is 'a'. If friendship.level is 'f' I don't even need to see what post.level is because that is the highest level of access.

As you posted I think all posts would match, since friendship.level all are 'a' or 'f'.

Thanks.

[eluser]JanDoToDo[/eluser]
SELECT * FROM (friendships LEFT JOIN user_profiles on friendships.member2 = user_profiles.user_id)
LEFT JOIN posts ON friendships.member2 = posts.user_id
WHERE friendships.member1 = '1' AND friendships.level = 'f' OR (friendships.level = 'a' AND posts.viewable = 'a')
ORDER BY posts.date DESC

This work?

[eluser]rich.a.coy[/eluser]
Yes, that's it! I was so close a few posts back, I just needed to think about using parenthesis.

Is this the best way to get this level of detail in data? This seems complex to me and I'm just wondering about how CPU intensive this is. I'm expecting a community of about 10,000-20,000 people using the site.

Thanks again for all the help.

Rich