I have two table shopping_sessions and cart_addresses. cart_addresses is linked with shopping_sessions using foreign key. shopping_sessions table has various columns eg. company_id, is_active, order_number, and so on.
ShoppingSession Model
class ShoppingSession extends Model
{
............
public function cartAddresses()
{
return $this->hasMany(CartAddress::class, 'shopping_session_id');
}
}
Requirement
If a query string is passed for search ($q), it should either match with "order_number" column in shopping_sessions table or "address_1" column in cart_addresses table of a company that the user is logged in. $companyId has an integer value.
I did tried with the following code.
$query = $this->shoppingSessionModel->query()
->with('cartAddresses');
if ( ! empty($q)) {
//search by order number
$query->where('order_number', 'LIKE', "%$q%");
//search by shipping address
$query->orWhereHas('cartAddresses', function($q2) use ($q) {
$q2->where('address_1', 'LIKE', "%$q%")
->where('is_shipping', 1);
});
}
$query->where('company_id', $companyId)
->where('is_active', 0)
->whereNotNull('order_number');
return $query->orderBy('updated_at', 'DESC')
limit);
What is Wrong in this?
When a search query is passed, it retrieves all COMPANY Records matching the text but I want to filter records of single company only. I want to add filter search query and company_id as well.
Issue In short:
I want to fetch records of single company but it is returning records of all company.
Any help would be very much appreciated.
PS: When I debug the SQL query using toSql() it output the following
select * from `shopping_sessions` where (`order_number` LIKE ? or exists (select * from `cart_addresses` where `shopping_sessions`.`id` = `cart_addresses`.`shopping_session_id` and `address_1` LIKE ? and `is_shipping` = ?) and `company_id` = ? and `is_active` = ? and `order_number` is not null) and `shopping_sessions`.`deleted_at` is null order by `updated_at` desc
Try like this:
if ( ! empty($q)) {
//search by order number
$query->where(function ($query) use ($q) {
$query->where('order_number', 'LIKE', "%$q%");
//search by shipping address
$query->orWhereHas('cartAddresses', function($q2) use ($q) {
$q2->where('address_1', 'LIKE', "%$q%")
->where('is_shipping', 1);
});
});
}
I think the issue is that the orWhereHas is being triggered when the companyId is not matched, so if you group the orWhereHas it should fix it.