CODEWITHSUNDEEP
phpsqllaraveleloquentrelationship
Leandro Parice
Leandro Parice

Reputation: 1096

Laravel - Get last record with condition in one-to-many relationship

I'm working with version 5.5 of Laravel and I have a one-to-many relationship. In the first table (Passengers) I have the passenger register

Passengers
id|name
1|Carlos
2|Pedro

In the second table I have the history of entries / exits of these passengers

Histories

id|passenger_id|type|created_at
1|1|in|2018-01-16 00:00:00
2|2|in|2018-01-16 01:00:00
3|3|in|2018-01-16 02:00:00
4|1|out|2018-01-16 03:00:00
5|1|in|2018-01-16 04:00:00
6|2|out|2018-01-16 05:00:00
7|3|out|2018-01-16 06:00:00
8|4|in|2018-01-16 07:00:00

What I would like to get is a count of all passengers with a type column being equal to 'in', based on a last interaction of each User, that is, my expected result would be this

id|passenger_id|type|created_at
5|1|in|2018-01-16 04:00:00
8|4|in|2018-01-16 07:00:00

count = 2 rows

Researching I was able to get to a SQL Query that caters to me, but I do not know how to apply it using Laravel

SELECT p.id, p.name, h1.id, h1.created_at, h1.type
FROM passengers p
JOIN histories h1 ON (p.id = h1.passenger_id)
LEFT OUTER JOIN histories h2 ON (p.id = h2.passenger_id AND (h1.created_at < h2.created_at OR h1.created_at = h2.created_at AND h1.id < h2.id))
WHERE h2.id IS NULL AND h1.type='in';

Can someone help me mount this query in Laravel or if I have another simple solution I appreciate

Upvotes: 1

Views: 1649

Answers (1)

Leandro Parice
Leandro Parice

Reputation: 1096

With the help of @edersoares, I found the solution, it will be registered if anyone has the same problem

$subquery = History::query()->selectRaw('MAX(id)')->groupBy('passenger_id');
$query = History::query()->whereIn('id', $subquery)->where('type', 'in');
$passengersIn = $query->get();

Upvotes: 1

Related Questions