Display Friends

[eluser]mStreet[/eluser]
I'm searching all over internet trying to use different methods but still no result. So if you know how to solve it please help me out.

[eluser]aquary[/eluser]
Then what are the query for both cases? I have to see it to know what happen in the database :-) echo $this->db->last_query() to get it.

[eluser]mStreet[/eluser]
Hehe alright, guess I just misunderstood you.

$this->db->last_query()

Code:

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

model friends

Code:

$query = $this
   ->db
       ->select('users.*, friendships.*')
    ->join('friendships', "friendships.user_id = users.user_id
    AND ( (friendships.user_id = " . $this->user->user_id . " )
    OR (friendships.requestee = " . $this->user->user_id . "))
    AND (friendships.status = 'accepted')")
    ->get('users');

[eluser]aquary[/eluser]
That's all? everything after the join part are missing.

[eluser]mStreet[/eluser]
Yeah, I noticed. But that's all :o

[eluser]aquary[/eluser]
That's... weird. Try chaining all the AND/OR in one line.... I always put a long query in that way and never found this kind of behaviour .-.

[eluser]PhilTem[/eluser]
Have you tried getting the users and friends separately and solely loop over the results from the friends table?
Maybe you give this one a try first to see if your set up works WITHOUT the join. So to speak you might have errors in your JOIN-part.

A general query should look like this (at least I'd write it down like this)

Code:

SELECT
    *
FROM
    `friends`
JOIN
    `users`
ON
    `uers`.`user_id` = `friends`.`source_id`
JOIN
    `users`
ON
    `users`.`user_id` = `friends`.`target_id`
WHERE
    `friends`.`target_id` = 'user_id'
OR
    `friends`.`source_id` = 'user_id'

Should return an array of arrays where either 'source_id' or 'target_id' is the ID of the active user.

Adopt that query to your needs, run it in simple-query mode, if this works, make it active-record compatible. And don't forget to report back

[eluser]mStreet[/eluser]
PhilTem, I got it working with an if statement, but it has to be a shorter way.
This is my model right now:

Code:

$query = $this
   ->db
    ->select('friendships.*, users.*')
    ->from('friendships')
    ->join('users', 'users.user_id = friendships.user_id')
    ->where('friendships.user_id = users.user_id')
    ->get();

$queryB = $this
   ->db
    ->select('friendships.*, users.*')
    ->from('friendships')
    ->join('users', 'users.user_id = friendships.friend_id')
    ->where('friendships.friend_id = users.user_id')
    ->get();
    
if ($query->row()->user_id !== $this->user->user_id)
{
  return $query->result();
}
else
{
  return $queryB->result();
}