Writing eloquent Query

I have two queries I will like to re-write. The first one is a raw query I would like to re-write using eloquent

DB::select('select * from asks WHERE amount IN (select MIN(amount) as lowest_ask from `asks` where `product_uuid` = ? group by `shoe_size_id`)', [$this->uuid]);

This gets the lowest ask for each shoe size if you need some context.

I tried many iterations but it all failed, here is the last one I did tried

Ask::where('amount', function($query) use ($product_id) {
  $query->select([
  	DB::raw('MIN(amount) as lowest_ask'),
	])
    ->where('product_uuid', $product_id)
    ->groupBy('shoe_size_id')
    ->get();
});

but it did not work

The second is a regular whereHasquery on a polymorphic many to many relationship.

$products->whereHas('brand', function ($query) use ($request) {
    $query->where('name', $request->get('brand'));
});

I will love to re-write this using a join. I have made several attempts but could not. Thanks for the help

LoaiDev

4 years ago

Level 1

If you want to get the one Ask that has the lowest price for a product you can do this

Ask::where('product_uuid', $product_id)
	->orderBy('amount', 'asc')
	->first();

If you have the product model itself (and have "asks" relationship) you can do something similar like this

$product->asks()
	->orderBy('amount', 'asc')
	->first();

seewhy

4 years ago

Level 8

@LoaiDev Thanks

DB::select('select * from asks WHERE amount IN (select MIN(amount) as lowest_ask from `asks` where `product_uuid` = ? group by `shoe_size_id`)', [$this->uuid]);

The above query works, I just need a way to write it in "the eloquent way" for two reasons:

I have to hydrate the model to access to its relations
I am having issues with Laravel Raw queries in postgres

AlexElementarteilchen

4 years ago

Level 9

You could split it in two queries.

The first one will get the list of lowest_ask:

$lowest_asks = DB::table('asks')
	->selectRaw('MIN(amount) AS lowest_ask')
	->where('product_uuid', $this->uuid)
	->groupBy('shoe_size_id')
	->pluck('lowest_ask')
	->toArray();

Not sure if the groupBy() will work like this because it's not part of the select statement (maybe needs some tweaking)

In the second one you can use the $lowest_ask like so:

Ask::whereIn('amount', $lowest_ask)->get();

Hope this helps!

seewhy

4 years ago

Level 8

@AlexElementarteilchen Thanks, I was try to avoid that but till I find out how to write it in a single query. this is how I have it

Ask::whereIn('amount', Ask::selectRaw('MIN(amount) as lowest_ask')
    ->where('product_uuid', $product_id)
    ->groupBy('shoe_size_id')
  	->pluck('lowest_ask')
    ->toArray()
)->get();

Thanks again @alexelementarteilchen

seewhy

4 years ago

Level 8

I also found another way from this questions https://laracasts.com/discuss/channels/eloquent/how-to-write-sub-query-in-eloquent. And that let to

Ask::whereIn('amount', function ($query) use ($product_id) {
    $query->selectRaw('MIN(amount) as lowest_ask')
        ->from(with(new Ask)->getTable())
        ->where('product_uuid', $product_id)
        ->groupBy('shoe_size_id')
        ->pluck('lowest_ask')
        ->toArray();
})->get();

1 like

Please or to participate in this conversation.