Multipart queries?

[eluser]JanDoToDo[/eluser]
Well you will have to join tables at some point as each table has different types of data and stuff. The only thing i would suggest is ONLY pull data that you actually need. i.e. do you need the whole of the user_profiles table or do you only need the name? if so change it to select friendships.*, user_profiles.name .. etc see what i mean?

Also, i use aliases as its easier to keep track, so use ... FROM (friendships AS f) ... and that means that you can refer to any column in the friendships table as "f" so, f.name or f.member2 etc..

[eluser]rich.a.coy[/eluser]
I saw your aliases in the code before but I took them out for my own readability. I'll go back and add them back in now that it's working. I'll also look at the query results and see what columns I don't need. I can probably eliminate 6 or 8 columns from the results set.

I think that with this query under my belt I feel more confident working on a few of the harder models in my application.

Thanks again.

Rich

[eluser]JanDoToDo[/eluser]
No problems... keep up the good work!

[eluser]rich.a.coy[/eluser]
Ok, found a small flaw in the SQL. It's returning the logged in users posts also. They should not be returned.

friendships.member1 is the logged in users id and friendships.member2 is the user_id of other person in the friendship.

$me is set to be the user id of the person logged in.

Here is the current statement:

Code:

SELECT f.member1, f.member2, f.level, up.user_id, up.photo_url, up.show_photo, p.*
        FROM ((friendships AS f) LEFT JOIN (user_profiles AS up) on f.member2 = up.user_id)
        LEFT JOIN (posts AS p) ON f.member2 = p.user_id  
        WHERE f.member1 = '".$me."'
        AND f.level = 'f'
        OR (f.level = 'a' AND p.viewable = 'a')
        ORDER BY p.date DESC

Any clue how the logged in users posts are getting added?

Thanks.

Rich

[eluser]rich.a.coy[/eluser]
I figured it out. I needed to add another set of parenthesis directly after the AND.

Below is the final version.

Code:

SELECT f.member1, f.member2, f.level, up.user_id, up.photo_url, up.show_photo, p.*
        FROM ((friendships AS f) LEFT JOIN (user_profiles AS up) on f.member2 = up.user_id)
        LEFT JOIN (posts AS p) ON f.member2 = p.user_id  
        WHERE f.member1 = '".$me."'
        AND (f.level = 'f'
        OR (f.level = 'a' AND p.viewable = 'a'))
        ORDER BY p.date DESC