Level 102
So which is the main table that they should be joined to ? Linked?
Oct 18, 2022
12
Level 1
How to join more than one table while performing search query in Laravel?
I'm trying to perform a search that involves 3 tables. Please help me to achieve this.
Code from the controller. Thank you in advance.
public function index(Request $request)
{
$linked = Linked::paginate(10); // Table-1
$customers = Customer::all(); // Table-2
$item = Item::all(); // Table-3
$query = $request->search;
if($query){
DB::table('linkeds as linked')->select(['customer_id'])->join('linkeds', 'customers.id', '=', 'linkeds.customer_id');
$customers = Customer::where('customer_name', 'like', "%{$request->search}%")->paginate(10);
$linked = Linked::where('supplier_ref_no', 'like', "%{$request->search}%")->orWhere('supplier_barcode', 'like', "%{$request->search}%")->paginate(10);
}
return view('linked.index', compact('linked', 'customers'));
}
Level 1
@Sinnbeck yes that's correct, Linked is the main table.
Level 102
@Syed1980 Ok so I assume it has columns that point to the two others?
$linked = Linked::query()
->join('customers', 'customers.id', 'linkeds.customer_id')
->join('items', 'items.linked_id', 'linkeds.id') //just guessing here
->where(function($query) use ($request) {
$query->where('supplier_ref_no', 'like', "%{$request->search}%")
->orWhere('supplier_barcode', 'like', "%{$request->search}%")
->orWhere('customer_name', 'like', "%{$request->search}%")
->orWhere('items.name', 'like', "%{$request->search}%"); //just guessing here
}
->paginate(10);
Level 1
@Sinnbeck sorry, it is giving an error.
you are almost right at guessing but vice versa. this is how I'm doing it and don't know where I'm wrong.
public function index(Request $request)
{
$linked = Linked::paginate(10); // Table-1
$customers = Customer::all(); // Table-2
$item = Item::all(); // Table-3
$query = $request->search;
if($query){
$linked = Linked::query()
->join('customers', 'customers.id', 'linkeds.customer_id')
->join('items', 'items.id', 'linkeds.item_id') //just guessing here
->where(function($query) use ($request) {
where('supplier_ref_no', 'like', "%{$request->search}%")
->orWhere('supplier_barcode', 'like', "%{$request->search}%")
->orWhere('customer_name', 'like', "%{$request->search}%")
->orWhere('items_name', 'like', "%{$request->search}%"); //just guessing here
}
->paginate(10);
}
return view('linked.index', compact('linked', 'customers'));
}
Level 102
@Syed1980 If you could give us the error, we might have an easier time helping. Also your query isnt the same as mine (some of it is missing)
Level 55
@Syed1980 missed $query in logical group
->where(function($query) use ($request) {
$query->where('supplier_ref_no', 'like', "%{$request->search}%")
->orWhere('supplier_barcode', 'like', "%{$request->search}%")
->orWhere('customer_name', 'like', "%{$request->search}%")
->orWhere('items_name', 'like', "%{$request->search}%"); //just guessing here
}
also be sure column names are unique across the joined tables. Otherwise specify the table name explicitly before the column
Level 1
@Sinnbeck sorry, I was trying to send screenshot, but with no success. Actually, It give error in the code itself "syntax error, unexpected '->' (T_OBJECT_OPERATOR), expecting ')'". I tried adding "(" and also try to remove -> but same error.
Level 1
Hello @sinnbeck, there is a small issue is coming with this code, I hope you can help me on this as well. The issue is while searching my display table is taking the image "item_image" from the items table, which is wrong. The "item_image" is supposed to be fetched from linkeds table.
Level 102
@Syed1980 Just prefix it with the table name
->select('linkeds.item_image')
//or
->select('linkeds.*')
Level 1
@Sinnbeck THANK YOU AGAIN. This ->select('linkeds.*') works like a charm. Since yesterday I was trying with the first one.
Level 104
How are the three Models (tables) related, if at all?
Level 1
I forgot to add " $query " before ->where because of which I was getting an error.
Please or to participate in this conversation.