+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: 3 level join using AR (/showthread.php?tid=16706)
3 level join using AR - El Forum - 03-14-2009
[eluser]stevenL[/eluser]
Hey all,
I am trying to wrap my brain around how to set up a CI model for the following structure.
My basic object is called a "project". A project can contain multiple images and can exist in multiple categories.
So I have 3 tables:
---------
categories
---------
projects
---------
images
---------
Each has a foreign key relating to the table above it.
When a category link is clicked, I need to display a list of projects, ordered by a sort_order column in the projects table. Each project in the resulting list would contain the row from the projects table as well as all the associated images from the images table.
Not sure where to start in the model. Should this be a multi-level join? Or single queries concatenated into a multi-dimensional array? Any help appreciated.
3 level join using AR - El Forum - 03-14-2009
[eluser]stevenL[/eluser]
I have the following in my model:
Code:
function get_projects_by_category($category_id)
{
$project_cat_id = $category_id;
$this->db->select('*');
$this->db->from('categories');
$this->db->join('projects', 'projects.category_id = categories.category_id');
$this->db->join('images', 'images.project_id = projects.project_id');
$this->db->where('categories.category_id', $project_cat_id);
$this->db->order_by("projects.project_order", "desc");
$query = $this->db->get();
return ($query->result());
}3 level join using AR - El Forum - 03-14-2009
[eluser]bretticus[/eluser]
Are you using MySQL? If so checkout the GROUP_CONCAT() MySQL documentation. If you have multiple image records per project record, you will get multiple records per image when using JOINs. You can programmatically suppress duplicate lines too.
3 level join using AR - El Forum - 03-14-2009
[eluser]stevenL[/eluser]
Thanks for replying. Yes, its mySQL.
As I said, I am getting multiple rows per project. Can you give me more detail regarding:
Quote:You can programmatically suppress duplicate lines too.
3 level join using AR - El Forum - 03-14-2009
[eluser]bretticus[/eluser]
[quote author="stevenL" date="1237086257"]Thanks for replying. Yes, its mySQL.
As I said, I am getting multiple rows per project. Can you give me more detail regarding:
Quote:You can programmatically suppress duplicate lines too.[/quote]
Certainly...
Since the image column is the only thing that changes from one record to the next, you can simply check to see if you have the same project id. The following is a simple illustrative example:
Code:
<?php
$query = $this->db->get('MyTable');
if ($query->num_rows() > 0) {
$last_project_id = 0;
foreach ($query->result() as $row) {
if ( $row->Project_ID != $last_project_id ) {
// show new project record
} else {
// show the next image
}
$last_project_id = $row->Project_ID;
}
}
?>This is a little "hacky" for my tastes. You may want to let the database handle it via GROUP_CONCAT().
3 level join using AR - El Forum - 03-14-2009
[eluser]stevenL[/eluser]
So I added group_by and that does supress redundant project info, but now I only get 1 image per project. Here's the latest Model function:
Code:
function get_projects_by_category($cat)
{
$project_cat = $cat;
$this->db->select('*');
$this->db->from('projects');
$this->db->join('categories', 'projects.category_id = categories.category_id','left');
$this->db->join('images', 'projects.project_id = images.project_id','left');
$this->db->group_by('projects.project_id');
$this->db->where('categories.category_id', $project_cat);
$this->db->order_by("projects.project_order", "asc");
$query = $this->db->get();
return $query->result();
}3 level join using AR - El Forum - 03-14-2009
[eluser]bretticus[/eluser]
[quote author="stevenL" date="1237089015"]So I added group_by and that does supress redundant project info, but now I only get 1 image per project. [/quote]
Right, that's why you need to do GROUP_CONCAT(). Because it's pretty much an exclusive function to mysql (don't quote me on that) CI doesn't have an abstraction for it (no CI Active Record.) You'd need to use a MySQL query instead:
Code:
$query = $this->db->query("YOUR QUERY");GROUP_CONCAT() on MySQL Manual
3 level join using AR - El Forum - 03-15-2009
[eluser]gyo[/eluser]
Have you considered using an ORM library?
There are some good ones from the community, to name a few:
DataMapper
IgnitedRecord
bye!