Level 102
Please show full. It looks like you are getting every single row from your database instead of letting the database handle the queries
Nov 25, 2021
15
Level 2
Skip filter if there is no result against query.
Hi there. I'm stuck with a little problem need help. I'm filtering records of a model and its relationships. The problem is If the statement becomes false the $matches variable becomes an empty array. How can I use the IF statement so the $matches variable doesn't become an empty array so it can go to the next condition? this is a sample code I can show full if anyone need.
$matches = Model::with('someRelation'); //can't use get() because want to filter data on relation.
if ($matches->get()->where('column', value)->count() > 0)
{
$matches->where('column', value);
}
if ($matches->whereRelation('someRelation', 'column', value)->count() > 0)
{
$matches->whereRelation('someRelation', 'column', value);
}
$matches become empty if the statement is false. whereRelation is a function introduced in laravel 8 replacement of whereHas. The First if working fine because i'm using get() and the chaining where how can I do the same thing with relation so the variable don't become empty.
Level 2
public static function autoMatchPropertyRequirement($property)
{
$filterCounter = null;
$matches = PropertyRequirement::with(['propertyRequirementDetail', 'customer'])->where([
'agency_id' => session('agency_id'),
'purpose' => $property->purpose,
'category_id' => $property->category_id,
'sub_category_id' => $property->sub_category_id,
'urgency' => $property->urgency
])->whereRelation('propertyRequirementDetail', 'city', $property->address->city);
$filterCounter = 5;
if (!empty($property->price)) {
if ($matches->get()->where('min_price', '<=', $property->price)->count() > 0) {
$matches->where('min_price', '<=', $property->price);
$filterCounter++;
}
if ($matches->get()->where('max_price', '>=', $property->price)->count() > 0) {
$matches->where('max_price', '>=', $property->price);
$filterCounter++;
}
}
if (!empty($property->area)) {
if ($matches->get()->where('min_area', '<=', $property->area)->count() > 0) {
$matches->where('min_area', '<=', $property->area);
$filterCounter++;
}
if ($matches->get()->where('max_area', '>=', $property->area)->count() > 0) {
$matches->where('max_area', '>=', $property->area);
$filterCounter++;
}
}
if (!empty($property->address->location) && $matches->whereRelation('propertyRequirementDetail', 'location', $property->address->location)->count() > 0) {
$matches->whereRelation('propertyRequirementDetail', 'location', $property->address->location);
$filterCounter++;
}
if ($property->category_id === 1) {
if (!empty($property->propertyDetail->bathrooms)) {
if ($matches->whereRelation('propertyRequirementDetail', 'min_bathrooms', '<=', $property->propertyDetail->bathrooms)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'min_bathrooms', '<=', $property->propertyDetail->bathrooms);
$filterCounter++;
}
if ($matches->whereRelation('propertyRequirementDetail', 'max_bathrooms', '>=', $property->propertyDetail->bathrooms)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'max_bathrooms', '>=', $property->propertyDetail->bathrooms);
$filterCounter++;
}
}
if (!empty($property->propertyDetail->rooms)) {
if ($matches->whereRelation('propertyRequirementDetail', 'min_rooms', '<=', $property->propertyDetail->rooms)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'min_rooms', '<=', $property->propertyDetail->rooms);
$filterCounter++;
}
if ($matches->whereRelation('propertyRequirementDetail', 'max_rooms', '>=', $property->propertyDetail->rooms)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'max_rooms', '>=', $property->propertyDetail->rooms);
$filterCounter++;
}
}
if (!empty($property->propertyDetail->parking_space) && $matches->whereRelation('propertyRequirementDetail', 'parking_space', $property->propertyDetail->parking_space)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'parking_space', $property->propertyDetail->parking_space);
$filterCounter++;
}
if (!empty($property->propertyDetail->year_build) && $matches->whereRelation('propertyRequirementDetail', 'year_build', $property->propertyDetail->year_build)->count() > 0) {
$matches = $matches->whereRelation('propertyRequirementDetail', 'year_build', $property->propertyDetail->year_build);
$filterCounter++;
}
}
return [
'matches' => $matches->get(),
'filterCounter' => $filterCounter,
];
}
Level 2
The problem is with the if on the relation. I'm tackling it on the model by using get() because we can chain on the model after using get but can't chain it with relation after using get because it already gets the collections. I know the problem but don't know the solution.
Level 102
@abdulrehmandar2234 you can use ->has('relationname') to check using the database
https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence
Level 2
@Sinnbeck Maybe you didn't get what's the problem. The problem is if the IF statement becomes false it will make my $matches variable to empty array. I don't want it. What I want is if the IF statement becomes false don't make my variable empty So, I can display some results to view. It's working fine if the statement is true.
Level 102
@abdulrehmandar2234 the you can add an else statement that sets the data like you want it
Level 2
@Sinnbeck, since the $matches will become empty, can't do anything will else I tried to explain the problem properly but still you may not understand it well. The problem is with the if on the relation. I'm tackling it on the model by using get() because we can chain on the model after using get but can't chain it with relation after using get because it already gets the collections. I know the problem but don't know the solution. I did this used $matches->get() to prevent $matches variable to becomes empty.
if ($matches->get()->where('max_area', '>=', $property->area)->count() > 0) {
$matches->where('max_area', '>=', $property->area);
$filterCounter++;
}
but can't do this on the relation
Level 5
@abdulrehmandar2234 I cannot understand the logic and what you are trying to achieve, but I will venture a guess.
You want to filter the results but also return a filter counter for each filter that was applied? For example, using this
if ($matches->get()->where('max_area', '>=', $property->area)->count() > 0) {
$matches->where('max_area', '>=', $property->area);
$filterCounter++;
}
you want to filter by max_area but also increment the $filterCounter if any properties where in fact filtered by max_area. Am I right?
If this is the case, I really don't think there is an efficient way to do this. I would forget that and increment the $filterCounter if a filter was applied, regardless if it filtered or not.
For example, I would do it like this (I haven't tested this)
$filterCounter = 5;
$matches = PropertyRequirement::with([
'propertyRequirementDetail',
'customer'
])
->where([
'agency_id' => session('agency_id'),
'purpose' => $property->purpose,
'category_id' => $property->category_id,
'sub_category_id' => $property->sub_category_id,
'urgency' => $property->urgency
])
->whereRelation('propertyRequirementDetail', 'city', $property->address->city)
->when(!empty($property->price), function ($query) use ($property, $filterCounter) {
$filterCounter++;
return $query->whereRaw('? BETWEEN min_price AND max_price', [$property->price]);
})
->when(!empty($property->area), function ($query) use ($property, $filterCounter) {
$filterCounter++;
return $query->whereRaw('? BETWEEN min_area AND max_area', [$property->area]);
});
return [
'matches' => $matches->get(),
'filterCounter' => $filterCounter,
];
What you are doing right now each time you call get(), is getting all the rows in a collection, filtering and counting that collection and then applying the filter again. This not efficient, like @sinnbeck said, you should do all the filtering in the database.
Level 2
@achatzi @sinnbeck let me explain what I am doing. This is a real estate project. Suppose someone want a property and apply filter of his choice like price, area etc. If there is no property with that price the db will return zero results. I want to show something to user if there is not exact price match Just skip the price filter and go to the areas one. The things which I want to match exactly are on the top category sub category city all other checks are not required to strictly matches that's why I'm counting the results in if condition. You just improve my code in a better nicer way but It will not solve the problem. I hope now you get what the problem is. If you have any solution please help.
Level 51
@abdulrehmandar2234 coming from the real estate space. I’d say that’s super confusing as a user.
Why not Just run a new query after if the first one is empty? Say for the zip code.
Level 2
@webrobert can you please give a little example.
Level 51
there's no pride in authorship but for the idea...
controller
$matches = Property::autoMatchPropertyRequirement($property);
$areaListsings = $matches['matches']->isEmpty()
? Property::where('zip code', $property->zipcode)->get()
: collect();
...
blade
@forelse($matches as $result)
// result ...
@empty
<div>no matching homes for your requirements.</div>
@endforelse
@if(!$matches && $areaListsings)
<h3>Here are some local listings</h3>
@foreach($areaListsings as $result)
// result ...
@endforeach
@endif
Edit, but I think you want to do more like Zillow's similar nearby
Level 51
I think you are going to have to write an algorithm for this. As I ran the Zillow example in my neighbor the most obvious place to purposely get no results was the price. I'm sure there are times when someone wants an 8 bedroom house. But I think people mostly tend to want more than they can pay for.
So maybe the real question is which area do you show them instead of the one they searched?
Because let's say, you run your search and remove the price filter. then the user is going to be like, "wtf I can't afford these?"
So two thoughts,
-
Do a query for the count for each filter first. I bet Zillow caches this daily or something. Then if it fails do the other query... whatever you decided.
-
Just run the query and if it fails, assume it failed in an order of priority ->
price, beds, sqftand remove one and run it again with, whatever else you decided. But again what are you going to show them?
As a user, I'd rather get "no results match your filters" Please don't tell me what you think I want to see.
Level 5
@abdulrehmandar2234 Now I understand. In this case I would use 2 query builders, like this
$matches = PropertyRequirement::with([
'propertyRequirementDetail',
'customer']
)
->where([
'agency_id' => session('agency_id'),
'purpose' => $property->purpose,
'category_id' => $property->category_id,
'sub_category_id' => $property->sub_category_id,
'urgency' => $property->urgency
])
->whereRelation('propertyRequirementDetail', 'city', $property->address->city);
$filter_builder = $matches;
$matches
->when(!empty($property->price), function ($query) use ($filter_builder, $filterCounter, $property) {
return $query
->when($filter_builder->where('min_price', '<=', $property->price)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('min_price', '<=', $property->price);
})
->when($filter_builder->where('max_price', '>=', $property->price)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('max_price', '>=', $property->price);
});
})
->when(!empty($property->price), function ($query) use ($filter_builder, $filterCounter, $property) {
return $query
->when($filter_builder->where('min_price', '<=', $property->price)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('min_price', '<=', $property->price);
})
->when($filter_builder->where('max_price', '>=', $property->price)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('max_price', '>=', $property->price);
});
})
->when(!empty($property->area), function ($query) use ($filter_builder, $filterCounter, $property) {
return $query
->when($filter_builder->where('min_area', '<=', $property->area)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('min_area', '<=', $property->area);
})
->when($filter_builder->where('max_area', '>=', $property->area)->count() > 0, function ($q) use ($filterCounter, $property) {
$filterCounter++;
return $q->where('max_area', '>=', $property->area);
});
});
But, I really don't think that it is efficient, because this means that you will make multiple queries in the database. For me, if no results are found with the selected filters that is what should be displayed, but I don't know your requirements so I hope this works.
Please or to participate in this conversation.