CodeIgniter Forums
Looping through left joined tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Looping through left joined tables (/thread-23236.html)



Looping through left joined tables - El Forum - 10-04-2009

[eluser]jshultz[/eluser]
I've got a small problem: I've got a model that's doing a left join on two tables. A projects table and a tasks table. It's supposed to echo out the project, then echo out all the tasks associated with that project. Instead,it's repeating the project name for each task row. Here's the code: http://stikked.com/view/25802955 do you think a second foreach loop would work? inside the first foreach loop?

VIEW:
Code:
<?php $currentProjectID = NULL; ?>
<?php if(count($projectPlusTasks) > 0) : foreach ($projectPlusTasks as $row): ?>
    
        <?php if ( $currentProjectID != $row->p_id ): ?>
        <tr>
            <td>
                <span class="project_name">Project: &lt;?= $row->p_name?&gt;</span>
            </td>
            <td>
                Due Date: &lt;?= $row->p_due_date?&gt;
            </td>
            <td>
                
            </td>
            <td>
            </td>
        </tr>
        &lt;?php endif;?&gt;
        <tr>
            <td>
                <strong>Task:</strong>&lt;?= $row->task?&gt;
              
            </td>
            <td>
                Due Date: &lt;?= $row->t_due_date?&gt;
            </td>
            <td>
                
            </td>
            <td>
            </td>
        </tr>
        <tr>&lt;form id="submit_Tasks" action="/site/submit_tasks" method="post"&gt;
            &lt;?php echo form_hidden('user_id', $user_id); ?&gt;
            &lt;input type="hidden" name="project_id" value="&lt;?=$row-&gt;p_id?&gt;">
            <td>New Task: &lt;input type="text" id="task" name="task" /&gt;&lt;/td>
            <td>Date: &lt;input type="text" id="&lt;?=$row-&gt;p_id?&gt;" class="datepicker" name="t_due_date" /></td>
            <td>&lt;input type="submit" name="submit" value="Submit" /&gt;&lt;/td>
            &lt;/form&gt;&lt;/tr>
        &lt;?php endforeach; ?&gt;
        
        &lt;?php else : ?&gt;
            
            <p class="notice">No Projects Found. Why don't you add one?</p>
            
          &lt;?php endif; ?&gt;
MODEL:
Code:
/* Get all Projects and Tasks by Project ID */
function tasksByProjectId() {
    
    $this->db->select('*');
    $this->db->from('projects');
    $this->db->join('tasks', 'tasks.projectid = projects.p_id', 'left');
    $query = $this->db->get();
    return $query->result();
}



Looping through left joined tables - El Forum - 10-04-2009

[eluser]kgill[/eluser]
When you use joins in a one to many relationship you get repeating columns, that's the nature of a database and how it's supposed to work. You're one step away from the solution though, you're already comparing if the currentProjectID is not equal to the p_id, but you aren't storing the p_id in currentprojectid so each time it loops it's always true and will always print.


Looping through left joined tables - El Forum - 10-05-2009

[eluser]sophistry[/eluser]
hi jshultz...

this is how i've approached joined output. maybe it will help you:
Code:
// this is a very simple sub-summary function that will give you nested arrays
//  call it like this
// $res is a ci query result object
$new_array = nest_flat_records($res,'name_of_field_result_is_sorted_on','name_of_field_in_joined_table');

// take the output from a join
    // (where the records are duplicated except
    // for one data point) and nest the
    // desired data point $pluck_out
    function nest_flat_records($res,$group_by_value,$pluck_out)
    {
        $new = array();
        $sub = array();
        $prev_group_by_value = '';
        foreach($res as $k=>$r)
        {
            if ($r->$group_by_value === $prev_group_by_value)
            {
                // add a sub item
                $sub[] = $r->$pluck_out;
            }
            else
            {
                // it's first record or the next group
                if (! $k) // first
                {
                    $kp = $k;
                    $sub[] = $r->$pluck_out;
                    unset($r->$pluck_out);
                    $new[$kp] = $r;

                }
                else // subsequent
                {
                    // store the previous group's
                    // sub array, then re-initialize it
                    
                    $new[$kp]->pluck_out = $sub;
                    $kp = $k;
                    $sub = array();
                    $sub[] = $r->$pluck_out;
                    unset($r->$pluck_out);
                    $new[$k] = $r;

                }
            }
            $prev_group_by_value = $r->$group_by_value;
        }    
        // cap it with the last one
        $new[$kp]->pluck_out = $sub;
        return $new;
    }



Looping through left joined tables - El Forum - 10-05-2009

[eluser]sophistry[/eluser]
oh yeah, and you might look at GROUP_CONCAT if you have mysql 4.1+ - that would let you do something similar in SQL:

http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

EDIT: be sure to read the comments at the bottom of that page: there is a limitation of 1024 characters when using group_concat


Looping through left joined tables - El Forum - 10-05-2009

[eluser]jshultz[/eluser]
Sweet! Thank you for all your help. This has been very informative. Smile