Level 73
Not sure what it is you want here, but the where should be before the orderBy.
Nov 6, 2022
9
Level 3
Help with MIN in query
I am trying to figure out how to get the records with the lowest value in a db column in a join query.
Part of the query is
// GET THE DEFAULT IMAGE IN THE JOIN TABLE
->join('gallery_photos as gp', function ($join) {
$join->on('gp.gallery_id', '=', 'gallery.id')
->orderBy('gp.sort', 'asc')
->where('gp.sort', '=', 1);
})
// GET THE DEFAULT FALLBACK IMAGE RECORD
->join('photos', function ($join) {
$join->on('photos.id', '=', 'gp.photo_id');
})
The part where gp.sort = 1 is where I am stuck. Perhaps during the user sorting or deleting there is no sort = 1 so I need to get the lowest possible sort value.
Thanks
Level 5
there is ->min('column_name') method. However, there is no guarantee that the min value is unique, which the join need, perhaps, give us the whole schema of your other table and what exactly you want to achieve (the full one)
Level 3
I am trying to get the lowest value in the DB because hard coded 1 value might not exist. The user might have a lowest value of 5 so thats why i am trying to use MIN
It now looks like:
// GET THE DEFAULT IMAGE IN THE JOIN TABLE
->join('gallery_photos as gp', function ($join) {
$join->on('gp.gallery_id', '=', 'gallery.id')
->where('gp.sort', \DB::raw(MIN('gp.sort')));
})
Results in "min(): Argument #1 ($value) must be of type array, string given"
Level 3
@lumethys Basically the users gallery must have 1 reference image in a paginated result. So for the sake of the question a user can not delete every image so there is always a minimum of 1 so there is always 1 record or more with a sort value, so I need to get the lowest possible value and then i get the image.
Its working properly with the hard coded value because i made sure all gp.sort has a value of 1 for testing but in live situation that my not be the case
Level 5
@movepixels take a look at this
Level 3
@lumethys I looked at it but all makes no sense. I see every google search for help using select MIN or MAX in the fields, I am not using the "sort" field for anything, I am not selecting it
Select fields..... all have the DB::raw like so from all the google results serching for help
->select('products.id', 'products.name', \DB::raw("MAX(skus.price) AS max_price"),
I am not selecting my sort field and not being a SQL master i have no clue.
The query works with hard coded sort => 1, but I want to get the min value I am simply trying to swap 1 with the lowest sort value.
I see example like so:
return \DB::table('products')
->select('products.id', 'products.name', \DB::raw("MAX(skus.price) AS max_price"), \DB::raw("MIN(skus.price) AS min_price"))
->join('skus', 'products.id', '=', 'skus.product_id')
->groupBy('products.id')
->get();
Maxes sense but the max_price and min_price are never used after. They I assume are returned values from the query but in my case I need to use the values.
Level 5
@movepixels the answer i give you had the where clause also, you did not include it in your query
Level 3
My query is a paginated query, should have mentioned that, so its for a public "listings" page so that includes a photo for each record found for the query. Users are able to sort their photos in the backend so by default the sorting starts from 1, so its easy to say get photo where sort = 1 but if they delete the photos with sort = 1 then the pagination hardcoded query where sort = 1 will not be in the listing result. So thats why i am trying to get the photo with the lowest sorted value
Level 3
$query = \DB::table('subscriptions as subs')
->join('profiles', function ($join) {
$join->on('subs.model_id', '=', 'profiles.id')
->where('profiles.status', 100);
})
// GET THE DEFAULT GALLERY
->join('gallery', function ($join) {
$join->on('gallery.album_id', '=', 'album.id')
->where('gallery.default', true);
})
// GET THE DEFAULT IMAGE IN THE JOIN TABLE
->join('gallery_photos as gp', function ($join) {
$join->on('gp.gallery_id', '=', 'gallery.id')
->where('gp.sort', 1);
// ->where('gp.sort', function($query){
// $query->selectRaw("MIN(gp.sort)")->limit(1);
// });
})
More stuff.... conditions and then
$records = $query->paginate(24)->withQueryString();
Please or to participate in this conversation.