I have two tables in db, "deals" and "deals_meta" connected between them with a foreign key. In the "deals" table i have general information about a deal like, title, description, category etc., and in the "deals_meta" i have other specific details like, location, number of bedrooms, price, booking date and so on.
I have a form with multiple fields, title, details, booking date, price range and so on.
My problem is that i can't retrieve from db the deals based on the search query.
The code that i use to retrieve the queries looks like this:
$deals = Deals::where('deals_providers', $provider)->where('deal_types', $dealType)->with('meta')
->where(function ($query) use ($terms, $dealType) {
$query->whereHas('meta', function ($query) use ($terms, $dealType) {
$search = $query->where('deal_type', 'like', '%' . $dealType . '%');
if ($terms['location']) {
$search = $query->orWhere('location', 'like', '%' . $terms['location'] . '%');
}
if ($terms['price_from']) {
$search = $query->orWhere('price', '>', '%' . $terms['price_from'] . '%');
}
if ($terms['price_to']) {
$search = $query->orWhere('price', '<', '%' . $terms['price_to'] . '%');
}
if ($terms['departs_from']) {
$search = $query->orWhere('departs_from', 'like', '%' . $terms['departs_from'] . '%');
}
if ($terms['duration']) {
$search = $query->orWhere('duration', 'like', '%' . $terms['duration'] . '%');
}
if ($terms['available_start']) {
$search = $query->orWHere('available_start', 'like', '%' . $terms['available_start'] . '%');
}
if ($terms['available_end']) {
$search = $query->orWhere('available_end', 'like', '%' . $terms['available_end'] . '%');
}
return $search;
});
if ($terms['title']) {
$query->orWhere('title', 'like', '%' . $terms['title'] . '%');
}
if ($terms['details']) {
$query->orWhere('details', 'like', '%' . $terms['details'], '%');
}
})->get();
Debug::data($deals);
If i search for title or details, it returns only the deals that have that title or contains that details but if i search for location or any other meta related term, it returns all the deals results from the db. What i'm doing wrong or how can i search corectly?
Thank you!