• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Join Logic

#1
[eluser]ibnclaudius[/eluser]
I`m having problems with a join. Check out the attached image.

I want to select all events from the colleges that a certain user id has. The field user_id on events table is the author of the event, not the current user.

I want also get the author name and the college username of the event.

http://img406.imageshack.us/img406/7195/codeigniter.png

How is that possible?

#2
[eluser]ibnclaudius[/eluser]
I can get the events info, but don`t know how to get only the events from the colleges the user participate...

Code:
public function get_events($user_id)
{
  $query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username')
        ->from($this->_table['events'])
        ->join($this->_table['colleges'], $this->_table['colleges'] . '.id = ' . $this->_table['events'] . '.college_id')
        ->join($this->_table['users'], $this->_table['users'] . '.id = ' . $this->_table['events'] . '.user_id')
        ->get();

  if ($query->num_rows() > 0) return $query->result_array();

  return FALSE;
}

#3
[eluser]usefulidiot[/eluser]
Code:
SELECT *
FROM events
JOIN users ON events.user_id = users.id
JOIN colleges ON events.college_id = colleges.id
WHERE events.user_id = 1

#4
[eluser]ibnclaudius[/eluser]
I don`t want to get the events from the same user_id...

Here what I have:

Code:
$query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username')
        ->from($this->_table['events'])
        ->join($this->_table['users_colleges'], $this->_table['users_colleges'] . '.user_id = ' . $user_id, 'INNER')
        ->join($this->_table['colleges'], $this->_table['colleges'] . '.id = ' . $this->_table['events'] . '.college_id AND ' . $this->_table['colleges'] . '.id = ' . $this->_table['users_colleges'] . '.college_id', 'INNER')
        ->join($this->_table['users'], $this->_table['users'] . '.id = ' . $this->_table['events'] . '.user_id', 'INNER')
        ->get();

  if ($query->num_rows() > 0) return $query->result_array();

  return FALSE;

Return this error:

Quote:Unknown column '1' in 'on clause'

SELECT `events`.`id`, `events`.`title`, `events`.`date`, `users`.`username` AS user_username, `colleges`.`username` AS college_username FROM (`events`) INNER JOIN `users_colleges` ON `users_colleges`.`user_id` = `1` INNER JOIN `colleges` ON `colleges`.`id` = `events`.`college_id` AND colleges.id = users_colleges.college_id INNER JOIN `users` ON `users`.`id` = `events`.`user_id`

The problem is with the quotes in 1, without it works... But I don`t know how to remove this ''

#5
[eluser]ibnclaudius[/eluser]
I tried putting a false param in select, but the problem persists...

#6
[eluser]weboap[/eluser]
looks to me like you will be doing like

Code:
public function get_events($user_id)
{
  $query = $this->db->select('users_colleges.*', FALSE)
               ->select('events.*', FALSE)
                ->join('events', 'events.college_id = users_colleges.college_id' )
                ->where('users_colleges.user_id', $user_id)
                ->get('users_colleges');

  if ($query->num_rows() > 0) return $query->result_array();

  return FALSE;
}


change events.user_id to events.author_id

#7
[eluser]ibnclaudius[/eluser]
Thanks weboap, helped a lot, could you please explain you code for me?

Here is the final, working:

Code:
$query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username', FALSE)
        ->join($this->_table['events'], $this->_table['events'] . '.college_id = ' . $this->_table['events'] . '.college_id')
        ->join($this->_table['colleges'], $this->_table['colleges'] . '.id = ' . $this->_table['events'] . '.college_id AND ' . $this->_table['colleges'] . '.id = ' . $this->_table['users_colleges'] . '.college_id', 'INNER')
         ->join($this->_table['users'], $this->_table['users'] . '.id = ' . $this->_table['events'] . '.user_id', 'INNER')
        ->where($this->_table['users_colleges'] . '.user_id', $user_id)
        ->get($this->_table['users_colleges']);

#8
[eluser]weboap[/eluser]
what do you mean with?

Code:
->join($this->_table['events'], $this->_table['events'] . '.college_id = ' . $this->_table['events'] . '.college_id')

read :
http://mysqljoin.com/

#9
[eluser]ibnclaudius[/eluser]
Ops, fixed:

Code:
$query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username', FALSE)
        ->from($this->_table['events'] . ', ' . $this->_table['users_colleges'])
        ->join($this->_table['colleges'], $this->_table['colleges'] . '.id = ' . $this->_table['events'] . '.college_id AND ' . $this->_table['colleges'] . '.id = ' . $this->_table['users_colleges'] . '.college_id', 'INNER')
         ->join($this->_table['users'], $this->_table['users'] . '.id = ' . $this->_table['events'] . '.user_id', 'INNER')
        ->where($this->_table['users_colleges'] . '.user_id', $user_id)
        ->get();


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.