Hi Devs,
I have a company models that has many ratings and a rating belongs to a company and in ratings i have a field ratings which takes the int rating from 0-5. So now I want to filter companies according to the max avg rating and min avg rating ?
My controller
public function index(Request $request)
{
$min_rating = Rating::min('rating');
$max_rating = Rating::max('rating');
$name = $request->name;
$companies = Company::where('name', 'like', '%' .$name . '%')
->with('ratings')
->paginate($request->get('per_page', 10));
return new CompanyCollection($companies);
}
i need help to finish the logic
By wanting to "filter companies according to the max rating and min rating", do you mean that you want to sort companies with the highest rating average descending ? For example:
Company | Rating
Apple | 4.6
Microsoft | 4.5
Amazon | 4.2
@piljac1 I mean to only show the companies average maximum rating per query and also win min per query so a sure can see only companies with maximum ratings or minimum ratings
Sorry, I don't understand what you're trying to achieve. Can you illustrate it with an example of what kind of input you will receive and what kind of output you're expecting?
I think I might have understood. So given the example I posted above, a user could specify that the min average rating should be 4.4. In that case, Apple and Microsoft would show, but not Amazon?
@piljac1 A user should be able to filter the list by the company minimum rating, and maximum rating.
for example there a test case already written for this
public function test_index_returns_based_on_name_search()
{
Company::factory()->count(20)->create();
Company::factory()->create(['name' => 'Test Name']);
$result = $this->getJson(route("companies.index") . '?name=Test');
$result->assertStatus(Response::HTTP_OK);
$result->assertJsonCount(1, 'data');
$result->assertJsonFragment(['total' => 1]);
$result->assertJsonFragment(['name' => 'Test Name']);
}
public function test_index_returns_based_on_minimum_rating()
{
Company::factory()->count(20)->create(['rating' => random_int(0, 3)]);
Company::factory()->count(5)->create(['rating' => 5]);
$result = $this->getJson(route("companies.index") . '?rating_min=4');
$result->assertStatus(Response::HTTP_OK);
$result->assertJsonCount(5, 'data');
$result->assertJsonFragment(['total' => 5]);
}
to explain further a user from the frontend is going to have a select input from filtering by maximun rating and then the controller handles that and return the qurey response.
I Am not sure if this is clear
I thought you said companies had many ratings. In this test, there's a rating column in the companies table. Is that right or a mistake?
@piljac1 yea its a mistake and it needs to be corrected, rating should be a relationship
How about:
$companies = Company::select('companies.*')
->with('ratings')
->leftJoin('ratings', 'companies.id', 'ratings.company_id')
->where('name', 'like', "%{$name}%")
->groupBy('companies.id')
->having(DB::raw('AVG(ratings.rating)'), '>=', $request->rating_min)
->paginate($request->get('per_page', 10));
This will give you all companies that have an average rating over or equal to the specified minimum rating.
That's the rough idea, but you would need to tweak the logic a bit only include the leftJoin, the groupBy and the having when $request->rating_min is present.
P.S., don't forget to add use Illuminate\Support\Facades\DB; at the top of your file.
Also, if you don't need to access ->ratings on your individual models, you can take out ->with('ratings').
You could just order by rating. Or do you want only the Max and only the minimum, how about the company's in the middle.
Where are you returning to.
@jlrdw sorry i meant I want to filter by company max average rating and also filter min avg rating
Do a double grouping, Group by Max, Group by minimum. That way Max will Show first in the results and minimum last. You can have more than one group by statement in a query.
If you don't need grouping, do where Max and min, then orderby.
That would be separate where statements by the way.
@jlrdw can you help with the example code for the first one (GROUPING)
@piljac1 @jlrdw if i can also do it the way @jlrdw said Do a double grouping, Group by Max, Group by minimum. That way Max will Show first in the results and minimum last. You can have more than one group by statement in a query.
i think that would fine as well
and would appreciate examples am lost in the jungle
Please or to participate in this conversation.