K
K
kedavr132021-07-27 20:57:23
MySQL
kedavr13, 2021-07-27 20:57:23

Search by average time in linked table?

There is a table with users, there is also a related table that stores dates in two columns in timestamp format.
Contact by hasMany.
How do I get all records from the first table whose average value between dates in minutes is greater than 10 is acceptable.
I tried to use TIMESTAMPDIFF, but I don't know if it will work with a 1:n relationship and how to shove it into the query builder.
there is a user table it has a relationship with the work_time table by hasMany
Work_time has the time_in and time_out columns It is
necessary to get all users whose average time in work_time is 20 according to the scheme all time_out - time_in / per number of such records

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
kedavr13, 2021-07-29
@kedavr13

Based on Konstantin 's answer , it turned out to collect such a request

$query
                ->innerJoin('histories' , 'users.id = histories.user_id')
                ->select('users.*, histories.user_id, avg(timestampdiff(minute,`histories`.`time_in`,`histories`.`time_out`)) as avgtime')
                ->groupBy('user_id')
                ->having('avgtime > ' . $this->wash_middle_time_more);

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question