I’ve been trying to do this since last week but still no luck.
What I want is to get average of distance, min of min distance, max of max distance, and sum the interaction duration by difference between interaction start and end, then group them by mobile number 1 and 2, but I want to make it like if the pair of mobile number 1 and 2 exist already doesn’t matter which one is mobile number 1 and which one is mobile number 2, it should be combined together
What I tried using eloquent is this:
$interaction_list = Interaction::select(
DB::raw('ROUND(latitude, 6) as latitude'),
DB::raw('ROUND(longitude, 6) as longitude'),
DB::raw('ROUND(AVG(distance), 2) as distance'),
DB::raw('MIN(ROUND(min_distance, 2)) as min_distance'),
DB::raw('MAX(ROUND(min_distance, 2)) as max_distance'),
DB::raw('SUM(TIMESTAMPDIFF(SECOND, interaction_started_at, interaction_ended_at)) as duration'),
Tracked User = mobile_no_1, Interaction = mobile_no_2
For the avg, min, max, and sum already correct but I want to make above result to be 1 row only for this data sample with duration would be 10 + 30 = 40 since it’s combined into 1 row.
I was able to get the result by using
select() instead of
groupBy() but then after getting other data in the table it becomes inaccurate since there’s interaction of mobile number 01234567892 with 0111111111, and the query combine that record together as 01234567892 with 01234567893 hence the result of record 01234567892 with 01111111 never appear in the table so I believe it’s wrong approach already.
from Newest questions tagged laravel-5 – Stack Overflow https://ift.tt/2KoiurO