Level 39
Start with a simple search, then makes it more complex step by step.
Ask for help if you get stuck on one step by giving your code, sample data & expected result.
Aug 16, 2022
9
Level 1
Search with multiple conditions across multiple relationships
I have 4 tables in my application. One main ItemLines table with 3 foreign keys that reference 3 other tables: ItemParent, ItemLines, and ShippingParent.
I'm finding it impossible to implement search. I want to give users a single search bar and let them search by sku (ItemLines table), order_id (ItemParent), name (ItemParent), or tracking_number (ShippingParent).
I'm using "like" syntax
'temLines::where(sku', 'like', '%' . $search_query . '%)' etc
I've tried all sorts of combinations of whereHas and where, but I haven't been able to get the results that I need.
What I'm hoping for is that when search is called, all of the fields that I want searched should be searched across all tables. If a match is found, the entire record with all relationships should be returned.
This is my current attempt:
public function order_search(string $search_query): array|Collection
{
return ItemLines::where('sku', 'like', '%' . $search_query . '%')
->with(['item_parent', 'products', 'shipping_parent'], function($query) use($search_query) {
$query->where('internal_order_id', 'like', '%' . $search_query . '%');
})
->get();
}
The error message is "message": "Illegal offset type", "exception": "TypeError",
Level 51
We need 3 parts here. 1- The condition for the main model
ItemLines::where('sku', 'like', '%' . $search_query . '%')
2- Eager loading all relationships that we need.
->with(['item_parent', 'products', 'shipping_parent'])
3- Specify to get only the records that has a relationship that apply to the searching query.
->orWhere(function($query) use ($search_query) {
$query->has('item_parent', fn($q) => $q->where('column_name', 'LIKE', '%' . $search_query . '%'))
})
The combination of the three steps would be
ItemLines::where('sku', 'like', '%' . $search_query . '%')
->with(['item_parent', 'products', 'shipping_parent'])
->orWhere(function($query) use ($search_query) {
$query->has('item_parent', fn($q) => $q->where('column_name', 'LIKE', '%' . $search_query . '%'))
->whereHas('item_parent', fn($q) => $q->where('column_name', 'LIKE', '%' . $search_query . '%'))
->orWhereHas('products', fn($q) => $q->where('column_name', 'LIKE', '%' . $search_query . '%'))
->orWhereHas('shipping_parent', fn($q) => $q->where('column_name', 'LIKE', '%' . $search_query . '%'));
})
->get();
1 like
Level 1
@MohamedTammam Thank you. I'm trying to build it up slowly, so here's what I have with just one join so far
public function order_search(string $search_query): array|Collection
{
return ItemLines::where('sku', 'like', '%' . $search_query . '%')
->with(['item_parent'])
->orWhere(function($query) use($search_query) {
$query->has('item_parent', fn($q) => $q->where('internal_order_id', 'like', '%' . $search_query . '%'));
})
->get();
}
Postman is giving me an error
"message": "Call to undefined method App\Models\ItemLines::item_parent()",
"exception": "BadMethodCallException",
...
The table is called item_parent. The model is called ItemParent and the ItemLines model has this method to join the tables
function itemParent(): HasOne
{
return $this->hasOne(ItemParent::class, 'id', 'item_parent_id');
}
Level 51
@mediabloke Convert that lines
->with(['item_parent'])
$query->has('item_parent' // ...
To
->with(['itemParent'])
$query->has('itemParent' // ...
Level 1
Updated to
return ItemLines::where('sku', 'like', '%' . $search_query . '%')
->with(['itemParent'])
->orWhere(function($query) use($search_query) {
$query->has('itemParent', fn($q) => $q->where('internal_order_id', 'like', '%' . $search_query . '%'));
})
->get();
I now get this error
"message": "SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select * from `item_lines` where (`sku` like %Sint% or ((select count(*) from `item_parent` where `item_lines`.`item_parent_id` = `item_parent`.`id` and `item_parent`.`deleted_at` is null) = (select * where `internal_order_id` like %Sint%))) and `item_lines`.`deleted_at` is null)",
Level 51
@mediabloke I update my answer. Please replace has with whereHas.
https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence
1 like
Level 10
Take a look a the last bit of the failed query you posted: (select * where `internal_order_id` like %Sint%)
There is no "from" clause
I think the issue is what you're passing as 2nd parameter in that $query->has() call. That subquery does not have a from so obviously it blows up. I don't think you're supposed to use it like that.
Check Laravel's documentation. https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence
To tweak the relation query you should use whereHas() instead of has()
1 like
Level 10
well, you beat me to it, lol...
1 like
Level 73
This is actually much easier than you might think.
Let's say that you want to search in two different tables that are related with a foreign key.
- Create a database view that contains the columns that you want to be able to search
- Create a migration for that view
- Create a model for that view
- Query it like you would a single table.
You can read more about that in this posts section about Views https://tray2.se/posts/database-design-part-2
2 likes
Please or to participate in this conversation.