Mixing
Laravel/SQL: How to fetch data from multiple table in a single query? that too using 'where'
Laravel/SQL: How to fetch data from multiple table in a single query? that too using 'where'
Working on a search functionality on Laravel App(Blog/Posts).
There are multiple types of posts (each having a separate table in the database)
Like Business posts, Social Life posts etc..
Below is the search function on SearchController
class SearchController extends Controller
{
public function search(Request $request, $query = null)
{
if($query == null)
return redirect()->route('home');
$search = Business::where([['title','like','%'.$query.'%'],['status','=',1]])
->orWhere([['description','like','%'.$query.'%'],['status','=',1]])
->paginate(10);
return view('front.search',[
'results' => $search,
'query' => $query
]);
}
}
So basically my question is how to add other types of Post's table also?
My main motive is that when someone searches for anything, the result should be fetched from all types of posts table(business, nature, life & so on..).
with()
Cannot use with(), as all the post types are in different tables and are not related (relationship not defined).
– Ravi Wadhwani
Jun 28 at 15:50
3 Answers
3
If you have a model called Book, like this:
class Book extends Model
{
/**
* Get the author that wrote the book.
*/
public function author()
{
return $this->belongsTo('AppAuthor');
}
}
Then you can retrieve all of your books with authors like this:
$books = AppBook::with(['author'])->get();
Check out Eager loading from Laravel documentation.
Hey @Milos, This is not what I am asking for.Sorry!
– Ravi Wadhwani
Jun 28 at 13:29
You have to maintain common id in both the table
NOTE: Join is the preferable method
$querys = DB::table('Business')->where([['Business.title','like','%'.$query.'%'],['Business.status','=',1]])
->orWhere([['Business.description','like','%'.$query.'%'],['Business.status','=',1]]);
$querys->join('socialtable','socialtable.userid','=','Business.userid');
// Just join the social table
$querys->where('socialtable.title', 'like','%'.$query.'%');
$result = $querys->paginate(10);
Getting error!Illuminate Database QueryException (23000) SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'brand' in where clause is ambiguous (SQL: select count(*) as aggregate from
businesses inner join socialtable on socialtable.user_id = businesses.user_id where (title like %adsasda% and status = 1) or (description like %adsasda% and status = 1) and socialtable.title like %adsasda%)– Ravi Wadhwani
Jun 28 at 13:33
businesses
socialtable
socialtable
user_id
businesses
user_id
title
status
description
status
socialtable
title
Did you got the solution? And sorry justnow i saw your comment
– Siva Ganesh
Jun 29 at 4:42
I think you modify your code after my answer, Did you please post your modified code
– Siva Ganesh
Jun 29 at 4:51
I have only modified the codes as suggested by you and am getting error as Illuminate Database QueryException (23000) SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'brand' in where clause is ambiguous
– Ravi Wadhwani
Jun 29 at 8:38
What about that error brand?? did you have any field named "brand" in your table
– Siva Ganesh
Jun 29 at 9:42
Just add table name before every field
$querys = DB::table('Business')->where([['Business.title','like','%'.$query.'%'],['Business.status','=',1]])
->orWhere([['Business.description','like','%'.$query.'%'],['Business.status','=',1]]);
$querys->join('socialtable','socialtable.userid','=','Business.userid');
// Just join the social table
$querys->where('socialtable.title', 'like','%'.$query.'%');
$result = $querys->paginate(10);
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
with()and then choosing a relationship– Thomas Moors
Jun 28 at 12:28