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:

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

$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 multiple rows with same 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 and wants to check out
• employee forgot to check out but forgot to check in earlier

and probably others