CodeIgniter Forums
Joining tables together in a model function - Printable Version

+- 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: Joining tables together in a model function (/showthread.php?tid=38457)

Joining tables together in a model function - El Forum - 02-08-2011

[eluser]jpidgeon[/eluser]
I am trying to pull together info from 3 tables using the active record class and return this result to my controller.

I am using 3 tables: criteria, tasks and data

I feed in a "task_id" to the crtieria table, and a "qms_id" to the data table.

Both the data and criteria tables are joined via "criteria_id"

A "task_name" is also joined in via the tasks and criteria tables.

The issue I have, is that on every row there is data present on the criteria and tasks tables, but not the data table.

Data is only inserted to the data table when I update using the form, but I still need the controller to feed out all the info, even if some of the fields are coming back blank or not existant.

This is because "qms_id" doesn't exist on the data table every time, therefore the model is returning any results.

When I change the 2nd where clause to or_where it works correctly, but doesn't relat correctly to each task.

This seems quite complicated, but if anyone has any ideas how I can still pull back everything, everytime this it would be much appreciated?

MODEL:

Code:
function include_criteria($task_id, $qms_id)
    {
        $this->db->where('quality_criteria.task_id', $task_id);
        $this->db->where('quality_data.qms_id', $qms_id);
        $this->db->join('quality_tasks', 'quality_tasks.task_id = quality_criteria.task_id');
        $this->db->join('quality_data', 'quality_data.criteria_id = quality_criteria.criteria_id', 'right');
        $this->db->order_by('quality_criteria.criteria_order', "ASC");
        
        $query = $this->db->get('quality_criteria');
        $return['rows'] = $query->result();
        
        $this->db->where('task_id', $this->uri->segment(3, 0));
        $query = $this->db->get('quality_criteria');
        $return['num_rows'] = $query->num_rows();
        
        return $return;
    }


Joining tables together in a model function - El Forum - 02-08-2011

[eluser]intractve[/eluser]
This to me seems like a database tables layout issue. It is common usage that any ID's or keys are in one table and related data, (if they have to be are broken up) are stored in separate tables and have the relevant ID's. The first option is to rethink this.

But you can work around your current design too... You will have to lookup each time if there are any entries for the qms_id and only then add the relevant where clause.

For Eg.
Code:
function include_criteria($task_id, $qms_id)
    {
        $this->db->where('quality_criteria.task_id', $task_id);
        if($this->check_qms($qms_id)) $this->db->where('quality_data.qms_id', $qms_id);
        $this->db->join('quality_tasks', 'quality_tasks.task_id = quality_criteria.task_id');
        $this->db->join('quality_data', 'quality_data.criteria_id = quality_criteria.criteria_id', 'right');
        $this->db->order_by('quality_criteria.criteria_order', "ASC");
        
        $query = $this->db->get('quality_criteria');
        $return['rows'] = $query->result();
        
        $this->db->where('task_id', $this->uri->segment(3, 0));
        $query = $this->db->get('quality_criteria');
        $return['num_rows'] = $query->num_rows();
        
        return $return;
    }
function check_qms($qms_id)
{
     $this->db->where('qms_id', $qms_id);
     $num_rows = $this->db->get('quality_data')->num_rows();

     //Next part is just to simplify checking in the other function or you could just return the number

     if($num_rows > 0) {
              return TRUE;
     } else {
              return FALSE;
     }

}


Joining tables together in a model function - El Forum - 02-08-2011

[eluser]Jaketoolson[/eluser]
I'm still not a 'convert' to structuring my queries using CI. I still use their DB Class to plow through the results, it's just that the actually query itself I still write out 'by hand' as this just makes more sense to me. Plus I interact with these databases in MS Access and therefore have to rewrite said queries to SQL/Access format.

Perhaps you need to start over from scratch and just write the entire query out and see if you get the desired results that way? My guess is that it's your table join, being a RIGHT join. Why not an INNER?

Code:
$sql = "SELECT * FROM table a
INNER JOIN table b ON b.id=a.id
INNER JOIN table c ON c.u_id=a.u_id
WHERE a.x = b.y
ORDER BY a.xx ASC";
$query = $this->db->query($sql);


Joining tables together in a model function - El Forum - 02-09-2011

[eluser]jpidgeon[/eluser]
Thankyou for your replys

intractve - I tried your suggestion but think adding an if statement will only parse the where clause for the entire function, not on a row by row basis which is the case

Just to explain further my reasoning for splitting up the table structure.

Of the 3 tables - criteria, data and tasks - we don't need to worry about tasks as I can join that fine.

The criteria table sets out how my form is dynamically displayed for each criteria item (related to a task) There can be up to 50 criteria items per task.

The data is then related to each criteria item by "criteria_id" which then populates the form fields set out by the criteria table.

What it basically means is that for every task there will also be upto 50 criteria rows that should always be shown, but the data which is joined won't always exist until it's inserted or updated. I need both to be pulled together even if the relational data doesn't exist.

jaketoolson - I did think about doing it this way. I'm using a right join so that duplicate data from the criteria table overpowers that of the data table on the left.

I haven't ever tried it before, but maybe the UNION feature could be used here?

My only other option is to pre-populate the data table when a task is created, ensuring the relational data already exists, but this doesn't seem very efficent if only 4 of the 40 criteria rows have content.

Joining tables together in a model function - El Forum - 02-09-2011

[eluser]Jaketoolson[/eluser]
Can you paste your table structure? Table joins aren't too complicated and I have 10-12 table joins at times, so its not impossible.

Joining tables together in a model function - El Forum - 02-09-2011

[eluser]jpidgeon[/eluser]
CRITERIA TABLE

criteria_id JOINED
criteria_name
criteria_description
criteria_order
criteria_kpi1
criteria_kpi2
criteria_kpi3
criteria_kpi4
criteria_kpi5
task_id JOINED

DATA TABLE

data_id
qms_id
staff_id
criteria_id JOINED
kpi1
kpi2
kpi3
kpi4
kpi5
comments
develop
dev_action
dev_result
dev_target
dev_completed

TASKS TABLE

task_id JOINED
task_name


So I'm normally sending in the data.qms_id and criteria.task_id, which would return the 40 or so rows in the criteria table, but as there are no rows currently existing in the data table - nothing shows. Rows of data are only added here once I save the form.

Joining tables together in a model function - El Forum - 02-09-2011

[eluser]Jaketoolson[/eluser]
i dont understand why you are running a select on a table with no data?

Joining tables together in a model function - El Forum - 02-09-2011

[eluser]Jaketoolson[/eluser]
SELECT *.c FROM ctiteria.c
INNER JOIN tasks t ON t.task_id=c.task_id
INNER JOIN data d ON d.criteria_id=c.criteria_id
WHERE c.task_id = x
AND d.task_id = y
HAVING COUNT(d.qms_id) > 0

gets all from criteria table innerjoins on tasks and data when there are more than 0 results found from table data on qms

Joining tables together in a model function - El Forum - 02-10-2011

[eluser]jpidgeon[/eluser]
I am running a select on the data table (even when it may be empty) as the data table populates my form fields, whereas the criteria table sets up how my form works and looks.

I've done it this way so I only have to use 1 foreach loop, rather than 1 inside another which I don't think is possible (I tried the other day and it didn't work correctly the way I did it)

This is part of my form view:

Code:
<?php $i = 0;  foreach($criteria as $item): ?>
<tr>    
        <td valign="top" class="criteria-column">
            <span class="criteria" id="criteria&lt;?php echo $item->criteria_order; ?&gt;">&lt;?php echo $item->criteria_name; ?&gt;</span>
            &lt;input name="qms_id[&lt;? echo $i; ?&gt;]" type="hidden"  value="&lt;?php echo $qms-&gt;qms_id; ?&gt;" />
            &lt;input name="data_id[&lt;? echo $i; ?&gt;]" type="hidden"  value="&lt;?php echo $item-&gt;data_id; ?&gt;" />
            &lt;input name="staff_id[&lt;? echo $i; ?&gt;]" type="hidden"  value="&lt;?php echo $qms-&gt;staff_id; ?&gt;" /></td>
        &lt;? if($count >= 1) { ?&gt;
        <td valign="top" class="kpi-column">
          &lt;?php if($item->criteria_kpi1 == "Y") { ?&gt;<select name="kpi1[&lt;? echo $i; ?&gt;]">
            <option value="NA" &lt;?php if (($item->kpi1) == "NA") {echo "selected=\"selected\"";} ?&gt;>NA</option>
            <option value="P" &lt;?php if (($item->kpi1) == "P") {echo "selected=\"selected\"";} ?&gt;>P</option>
            <option value="F" &lt;?php if (($item->kpi1) == "F") {echo "selected=\"selected\"";} ?&gt;>F</option>
  </select>&lt;? } ?&gt;</td>
        &lt;? } if($count >= 2) { ?&gt;
        <td valign="top" class="kpi-column">
          &lt;?php if($item->criteria_kpi2 == "Y") { ?&gt;<select name="kpi2[&lt;? echo $i; ?&gt;]">
            <option value="NA" &lt;?php if (($item->kpi2) == "NA") {echo "selected=\"selected\"";} ?&gt;>NA</option>
            <option value="P" &lt;?php if (($item->kpi2) == "P") {echo "selected=\"selected\"";} ?&gt;>P</option>
            <option value="F" &lt;?php if (($item->kpi2) == "F") {echo "selected=\"selected\"";} ?&gt;>F</option>
  </select>&lt;? } ?&gt;</td>
        &lt;? } if($count >= 3) { ?&gt;
        <td valign="top" class="kpi-column">
          &lt;?php if($item->criteria_kpi3 == "Y") { ?&gt;<select name="kpi3[&lt;? echo $i; ?&gt;]">
            <option value="NA" &lt;?php if (($item->kpi3) == "NA") {echo "selected=\"selected\"";} ?&gt;>NA</option>
            <option value="P" &lt;?php if (($item->kpi3) == "P") {echo "selected=\"selected\"";} ?&gt;>P</option>
            <option value="F" &lt;?php if (($item->kpi3) == "F") {echo "selected=\"selected\"";} ?&gt;>F</option>
  </select>&lt;? } ?&gt;</td>
        &lt;? } if($count >= 4) { ?&gt;
        <td valign="top" class="kpi-column colour-&lt;? echo $item->kpi4; ?&gt;">
          &lt;?php if($item->criteria_kpi4 == "Y") { ?&gt;<select name="kpi4[&lt;? echo $i; ?&gt;]">
            <option value="NA" &lt;?php if (($item->kpi4) == "NA") {echo "selected=\"selected\"";} ?&gt;>NA</option>
            <option value="P" &lt;?php if (($item->kpi4) == "P") {echo "selected=\"selected\"";} ?&gt;>P</option>
            <option value="F" &lt;?php if (($item->kpi4) == "F") {echo "selected=\"selected\"";} ?&gt;>F</option>
  </select>&lt;? } ?&gt;</td>&lt;? } ?&gt;
        <td valign="top">&lt;textarea value="" name="comments[&lt;? echo $i; ?&gt;]"&gt;&lt;?php echo $item->comments; ?&gt;&lt;/textarea&gt;&lt;/td>
          <td valign="top" class="edit-column">&lt;input &lt;?php if (!(strcmp("$item-&gt;develop","Y"))) {echo "checked=\"checked\"";} ?&gt; type="checkbox" name="development[&lt;? echo $i; ?&gt;]" value="Y"/></td>
</tr>
&lt;?php ++$i; endforeach; ?&gt;