A more eligible way to collapse multiple lines?
I'm new to codeigniter (although I suppose it's not exclusively a CI question) and there is a method in the model that fetches data from two tables joined by id.
Table 1 (tblclients) looks like this:
+----+------------+
+ id + c_name +
+----+------------+
+ 1 + Joe Bloggs +
+ 2 + Jim Bloggs +
+ 3 + Tim Bloggs +
+----+------------+
Table 2 (tblstars) looks like this:
+----+------------+
+ id + s_date +
+----+------------+
+ 1 + 27/01/12 +
+ 1 + 15/02/12 +
+ 1 + 18/02/12 +
+ 2 + 03/01/12 +
+ 2 + 11/02/12 +
+ 2 + 15/02/12 +
+ 3 + 01/01/12 +
+ 3 + 19/02/12 +
+----+------------+
I want to "collapse" the concatenated data into one line for each line in tblclients so that I can output, for example:
+----+------------+--------------------------------+
+ id + Name + Dates +
+----+------------+--------------------------------+
+ 1 + Joe Bloggs + 27/01/12 15/02/12 18/02/12 +
+ 2 + Jim Bloggs + 03/01/12 11/02/12 15/02/12 +
+ 3 + Tim Bloggs + 01/01/12 19/02/12 +
+----+------------+--------------------------------+
Now I "solved" the problem using the following in my model:
function get_clients_concat()
{
$query = $this->db
->select('tblclients.id, tblclients.c_name, GROUP_CONCAT(tblstars.s_date SEPARATOR "#") AS star_dates', NULL, FALSE)
->join('tblstars', 'tblstars.id = tblclients.id', 'left')
->order_by('tblclients.id')
->group_by('tblclients.id')
->get('tblclients');
return $query->result();
}
and then hack the array (created by GROUP_CONCAT) in my view and do some processing with it there ... but it seems to be really CLUNKY.
Is there a better solution?
source to share
If you want to get all this data in one request (with a unique ID for each row) then yes is the way to go.
If you need to sort or filter the results, you will run into performance bottlenecks when populating tables.
It seems strange - why don't you choose from tblstars
(join tblclients
) instead and then use application logic to index dates on c_name
/ id
?
<?php
// Select data from tables
$data = $this->db
->select('tblclients.id, tblclients.c_name, tblstars.s_date')
->join('tblclients', 'tblclients.id = tblstars.id')
->order_by('tblstars.id')
->get('tblstars');
// Index data by client id
// (keeping record of client name and dates array for each)
$clients = array();
foreach ($data->result() as $result)
{
if (empty($clients[$result->id]))
{
$clients[$result->id] = array(
'name' => $result->c_name,
'dates' => array($result->s_date)
);
}
else
{
$clients[$result->id]['dates'][] = $result->s_date;
}
}
return $clients;
source to share