Answer the question
In order to leave comments, you need to log in
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
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 questionAsk a Question
731 491 924 answers to any question