I'm trying to fetch data using Codeigniter 4 and query builder. I have a table structure like below

I want to retrieve data and output to the view as single array, combining two rows of records into one like below

my current model is below

    $builder = $this->db->table('attendance');
    $builder->select('id, employee_id, in_out, time');
    $builder->select('id, employee_id, in_out, CONCAT(DATE_FORMAT(time, "%r"), '.', DATE_FORMAT(time, "%r")) AS clock_time', FALSE);
    $builder->groupBy('employee_id');
    $query = $builder->get()->getResult();

    return $query;

I'm trying to figure out using CONCAT or is there a better way?

CodePudding user response：

you can resolve this by joining (left join) your table attendance with itself.

The columns in and out are aliases from the column time. The first SELECTgets you all in values by each employee, which by your example has always a value.

Now we only need to add the out part, which is done via a LEFT JOIN, querying the table attendance again.

this is the raw MySQL query, which you can see working in this sqlfiddle

SELECT
    `t1`.`id`,
    `t1`.`employee_id`,
    `t1`.`time` AS `in`,
    `t2`.`time` AS `out`
FROM
    `attendance` `t1`
LEFT JOIN(
    SELECT
        *
    FROM
        `attendance`
    WHERE
        `in_out` = 'out'
) t2
ON
    `t1`.`employee_id` = `t2`.`employee_id`
WHERE
    `t1`.`in_out` = 'in'

this can be "translated" into a CI query using the CI Query Builder Class. Note the use of table aliases t1 and t2 as well as the column aliases in and out

$builder = $this->db->table('attendance t1');
$builder->select('t1.id, t1.employee_id, t1.time as in, t2.time as out');
$builder->join('(SELECT * FROM attendance WHERE in_out = "out") t2', 't1.employee_id = t2.employee_id', 'left')
$builder->where(t1.in_out, 'in');
$query = $builder->get()->getResult();

return $query;

while this is to answer your question How to merge two rows with same column value into one row you will need to prepare for several time attendance issues, like

• multiple check-in/out from same employee at same day
• check out after midnight (next day)
• employee forgot to check in earlier and wants to check out
• employee forgot to check out earlier and wants to check in

and probably others