CodeIgniter Forums

Full Version: Is this a LEFT join by default ?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]cPage[/eluser]
Is this a LEFT join by default ?

Code:
function access($group_id)
{
  $this->db->select('*');
  $this->db->from('access');
  $this->db->join('groups', 'groups.id_group = access.group_id');
  $this->db->where('group_id',$group_id);
  $query = $this->db->get();
  return $query->result();
}

El Forum

[eluser]PhilTem[/eluser]
From the user's guide

Quote:$this->db->join();
Permits you to write the JOIN portion of your query:
Code:
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

$query = $this->db->get();

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id

Multiple function calls can be made if you need several joins in one query.

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.
Code:
$this->db->join('comments', 'comments.id = blogs.id', 'left');

// Produces: LEFT JOIN comments ON comments.id = blogs.id

El Forum

[eluser]cPage[/eluser]
It's funny how this example could not be more wrong.
It looks like blogs table have primary named id and the comments have a field named id too.

What is the link ? blogs.id = comments.id ? So the id field from comments is the primary key in blogs table. What if you have more than 1 foreign key ?

I do not work like that , it can be to messy.

For me , a real referencial integrity relation between 2 tables should be :

blogs.id_blog

comments.id_comment
comments.blog_id (foreigh key)

Code:
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.blog_id = blogs.id_blog');
$query = $this->db->get();

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.blog_id = blogs.id_blog

comments could be something else than a comment that come from a blog. It could be a comment from lets say table appointments :

Code:
$this->db->select('*');
$this->db->from('appointments');
$this->db->join('comments', 'comments.appointment_id = appointments.id_appointement');
$query = $this->db->get();

// Produces:
// SELECT * FROM appointments
// JOIN comments ON comments.appointment_id = appointments.id_appointement

Unless you want many comments tables. comments_blog, comments_appointements, comments_cake, comments_car, comments_home. etc.