Jump to content

What is the logic behind this?


Braxton

Recommended Posts

Logic? I have no idea when it comes to this, but I will explain as best I can and then if someone could point my into the right direction as to where I could read more about this, that would be super! I would search google or even the forums for this, but I have no idea of how I would even really word this or what keywords.

 

I have table?(friend_collective) with the columns id|receiver|sender|friend_flag the receiver and sender columns are simply user ids where as friend_flag is purely either Y or N. now then I have a second table(status_collective) with the columns id|uid|status_message|time_posted the uid being the user id, the rest are pretty obvious.

 

Now what I am basically trying to do is get all the status messages for the friends of a specific user. I know how I can achieve this in PHP, but for my own learning purposes and also cause I want to know if this is even possible to achieve with mysql, meaning less php.

 

Hope this makes sense. Not even sure if I worded this correctly.

Link to comment
Share on other sites

SELECT sc.status_message, u.name

FROM friend_collective fc

JOIN users u ON u.id = fc.receiver

LEFT JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $currentUserID AND fc.friend_flag = 'Y'

 

Untested, but should give you an idea of what you want to do. I've used a left-join for the status in case a user didn't have a status.

Link to comment
Share on other sites

SELECT sc.status_message, u.name

FROM friend_collective fc

JOIN users u ON u.id = fc.receiver

LEFT JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $currentUserID AND fc.friend_flag = 'Y'

 

Untested, but should give you an idea of what you want to do. I've used a left-join for the status in case a user didn't have a status.

 

Okay this has got me on the right track. Perfect actually. The only thing that I don't understnad is how I can add in a second Join or Left join to also return my status posts. What I have so far.

SELECT u.username, sc.status_message AS status, sc.time_posted AS time
FROM friend_collective fc
JOIN user_collective u ON u.id = fc.receiver
LEFT JOIN status_collective sc ON sc.uid = fc.receiver
WHERE fc.sender = 1 AND fc.friend_flag = 'Y'
ORDER BY sc.time_posted DESC
LIMIT 20;

This does return results of the friends of id 1 but not id 1's own status messages. if a friend doesn't have a status posted though, returns null. Upon trying to play around with adding a second own join I got some really nasty lookin errors in my client.

 

 

Link to comment
Share on other sites

All you're missing is the user's own status? I'd personally do that in a second query, otherwise you'll lose a few optimizations that query takes advantage of. It's possible to do it in a single query, but I doubt it's more efficient.

Link to comment
Share on other sites

If the user doesn't have a status, of course it's going to return null. What would you expect it to return?

 

If you want to skip users without a status message, change the LEFT JOIN to a JOIN. I even mentioned in my post that I used a left join in case a user doesn't have a status.

Link to comment
Share on other sites

I even mentioned in my post that I used a left join in case a user doesn't have a status.

Well that part i did misunderstand, reading back at it I'm still kinda confused how its worded, but that is irrelevant.  I get what you are saying now and I see how it works some what. I've been reading over the sticky about joins and I'm still kinda "lolwut?"

Link to comment
Share on other sites

Thanks mate. That helped much.  If I have the following line. Do I then have to JOIN the status_collective table again to get the posts of the user(id 1) maybe I was doing something wrong cause I would get each status message sometimes twice and even three times.

JOIN status_collective sc ON sc.uid = fc.receiver

Link to comment
Share on other sites

If you must do it in a single query, use a UNION.

 

 

SELECT 'myself' as username, status_message status, time_posted time FROM status_collective WHERE uid = $userID

UNION

SELECT u.username, sc.status_message, sc.time_posted

FROM friend_collective fc

JOIN user_collective u ON u.id = fc.receiver

JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $userID AND fc.friend_flag = 'Y'

ORDER BY sc.time_posted DESC

LIMIT 20;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.