Level 104
So, you want only the record at MAX(id) per group (grouped by departure_id)?
Jul 13, 2023
16
Level 3
Filtering data from table.
I have my database table image link above.
where the record should be grouped by departure_id and pick the latest record if the record has the same departure_id in the picture
id 291 and 290 have the same departure_id so I want only 291 records only be fetched from the table if the record has the same departure_id.
Level 74
Then you should use max and group by
Something like
SELECT MAX(id), departure_id
FROM table
GROUP BY departure_id
Level 3
@Tray2 This work fine but when ordered by id it doesn't work.
SELECT MAX(id), departure_id
FROM table
GROUP BY departure_id
ORDER BY id DESC
it fetches all data
Level 74
@somenet77 Try this
SELECT MAX(id), departure_id
FROM table
GROUP BY departure_id
ORDER BY 1 DESC
1 like
Level 3
@Tray2 working like a charm but I got one issue with MySQL
SQL Error [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
while I added an extra column in the select list
select MAX(id), package_id, departure_id, start_date from user_packages
where trek_group = 'group'
group by departure_id
order by 1 desc
Level 74
@somenet77 You need to add the rest of the columns to the group by statement.
group by package_id, departure_id, start_date
Level 104
@somenet77 are you sure you are getting the correct package_id and start_date values; because there is no reason why they should automatically come from the record with max(id) - it might be the correct values, but not guaranteed. This was the nature of my earlier question because the nature of the query changes depending on the columns you need.
For example, if you want the entire record having the maximum ID, I would expect the query to look like this:
SELECT up1.*
FROM user_packages AS up1
JOIN (
SELECT departure_id, MAX(id) as max_id
FROM user_packages
GROUP BY departure_id
) up2
ON up1.id = up2.max_id
Level 3
Level 104
@somenet77 Nothing wrong with the @tray2 query; just be aware how the database would handle extra columns if you queried for them. I don't know why there are two "missing" records - I would need to see the entire data to understand why the JOIN excluded them.
Level 3
Level 104
@somenet77 the record with ID 176 should not be in the data; it has the same departure_id and therefore is replaced by ID 177
Similarly, 224 has the same departure_id as 290 and 291 and is therefore is replaced by the latter.
Level 3
@tykus is it possible to join the other table? I have user_package_payments table like this so I also need to get the total payment from this table also.
Level 3
@tray2 @tykus Now all problems are sorted but one last issue comes with hasMany
UserPackage::with([
'user:id,name,email,country_id',
'user.country:id,nicename',
'package:id,name',
'addons:id,user_package_id,name,count',
'payments:id,user_package_id,payment_type,amount',
])
->selectRaw('max(id), user_id, departure_id, package_id, start_date')
->where('trek_group', 'group')
->take(10)
->groupBy(['user_id', 'departure_id', 'package_id', 'start_date'])
->orderByRaw('1 desc')
->get()
->dump();
user and package relation work fine but addons and payments return 0 results and my model is like this
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class UserPackage extends Model
{
use HasFactory, SoftDeletes;
protected $guarded = [];
protected $casts = [
'start_date' => 'date:Y-m-d',
'end_date' => 'date:Y-m-d',
'is_paid' => 'boolean',
'arrival_date' => 'date:Y-m-d',
'arrival_time' => 'date:H:i',
'appointment_date' => 'date:Y-m-d',
'appointment_time' => 'date:H:i',
'unique_checkout_hotel' => 'json',
];
public function user()
{
return $this->belongsTo(User::class)->withTrashed();
}
public function package()
{
return $this->belongsTo(Package::class)->withTrashed();
}
public function addons()
{
return $this->hasMany(UserPackageAddon::class);
}
public function payments()
{
return $this->hasMany(UserPackagePayment::class);
}
}
Level 11
@somenet77 I just tweeted about this yesterday.
- You can optimize the loaded data by specifying the exact columns you need.
- But if you skip the RELATIONSHIP column
xxxxx_id, it will not give you the data at all.
Level 3
@PovilasKorop in my case id using the max function so it's not working.
Level 11
@somenet77 sorry to bring this discussion to the beginning, but maybe you could use a specific relationship like latestOfMany() here? Read in the Laravel docs.
In your case it would probably be in User model:
public function latestPackage(): HasOne
{
return $this->hasOne(Package::class)->latestOfMany();
}
And then load User::with('latestPackage') and other relationships
Also, you could watch my video: 5 Different Ways to Get Latest Row of Relationship
Please or to participate in this conversation.