Display Friends

[eluser]mStreet[/eluser]
Hello!

I'm currently just fooling around with Codeigniter, but I'm stuck trying to build a friendship system.

My Friendships database looks like this:
User_id, Friend_id, Status

If Friend_id or User_id is the logged in person then fetch the one that isn't the logged in ID and output say username instead of friend_id in the view.

I've tried a lot of different methods but just can't seem to get it working.

Please help me out, thanks!

[eluser]aquary[/eluser]
What have you tried? we need some base to start. There is also something about how you store relationship. Do you store 1 relation (X <-> Y = X and Y are friends) or 2 (X -> Y, Y -> X = X is a friend of Y, and Y is a friend of X)?

[eluser]mStreet[/eluser]
This is what my model looks like.

Code:

function friends()
{
  $query = $this
   ->db
    ->join('friendships', 'friendships.user_id = users.user_id')
    ->where('friendships.user_id', $this->user->user_id)
    ->or_where('friendships.friend_id', $this->user->user_id)
    ->where('friendships.status', 'accepted')
    ->get('users');
    
  return $query->result();
}

I'm sorry, I don't understand your question with the x's and y's. Could you please clarify it for me?

[eluser]aquary[/eluser]
How you store data when X is a friend of Y.

User_id, Friend_id, Status
// either this
X_id, Y_id, 1
// or these, two way relationship
X_id, Y_id, 1
Y_id, X_id, 1

The second case is easier to lookup for relationships since it'll be just "Looking for those who are my friend (User_id is my id)" while the first case is easier for maintenance, but hard on "looking for those whois my friend, and I'm their friend (User_id is my id OR Friend_id is my id)".

From the code, you are seems to be the first one.

* El Forum on thinking mode.

[eluser]aquary[/eluser]
Just to make it easier to read >_>

Code:

function friends()
{
    // You can merge the join and where together,
    // which is faster for the query since your main table here is "users".
    // Putting where somewhere else would slow it down.
    $query = $this->db
    // you could also try running this part as a single query as see if the result is valid
    ->join('friendships', "(friendships.user_id = users.user_id)
          AND (
            (friendships.user_id={$this->user->user_id})
             OR (friendships.friend_id={$this->user->user_id})
          )
          AND (friendships.status='accepted')
      ")
    // Since I don't know how you store login time, I put it this way. The last active is a unix timestamp
    // The where will filter only those who has done something in the last 5 minutes
    ->where('users.last_active>='.time()-300, NULL, FALSE)
    ->get('users');
  return $query->result();
}

...
Actually, I didn't even know yet what is your current result >_>?

edit: change the where condition

[eluser]mStreet[/eluser]
Yes, I want to do it like the first one you mentioned. The second one just seems a bit too much. It has to be possible! Although, I have no clue how to do it.

Thanks!

[eluser]mStreet[/eluser]
After trying your code I got an error:

Code:

A Database Error Occurred

Error Number: 1054

Unknown column 'users.user_id)' in 'on clause'

SELECT * FROM (`users`) JOIN `friendships` ON `friendships`.`user_id` = `users`.`user_id)`

Filename: C:\wamp\www\developers\system\database\DB_driver.php

Line Number: 330

[eluser]aquary[/eluser]
Did you forget something? normally the query should be..

Code:

// The last backtick should be inside.
SELECT * FROM (`users`) JOIN `friendships` ON `friendships`.`user_id` = `users`.`user_id`)

And the full one would be...

Code:

// replace all number by your real data
SELECT * FROM (`users`) JOIN `friendships` ON (`friendships`.`user_id` = `users`.`user_id`) AND (
(`friendships`.`user_id`=12345) OR (`friendships`.`friend_id`=12345)) AND (`friendships`.`status`='accepted'))
WHERE users.last_active>=1357956464

[eluser]mStreet[/eluser]
The error is gone. This is what it shows me:

http://localhost/codeigniter/friends - Logged in as John
My friends: Mitchell = Correct

http://localhost/codeigniter/friends - Logged in as Mitchell
My friends: Mitchell

[eluser]aquary[/eluser]
check the queries of both cases, what are the different between them ?