Laravel multiple keywords search on multi table with operator AND/OR

I have an advanced search form that filter results from mySql database. There is one part which is a keyword search that should match any text fields on all related tables.

This is the said search form code.

<form action="/search" method="POST">
   Keyword: <input type="text" name="keyword">
   Matching: <input type="radio" name="keywordControl" value="AND"> Match every words<br>
             <input type="radio" name="keywordControl" value="OR"> Match any words<br>
 <input type="submit">
</form>

Tables:

CREATE TABLE `User` (
  `id` INT,
  `firstName` VARCHAR(100),
  `lastName` VARCHAR(100),
  `email` VARCHAR(100),
  `address` VARCHAR(400)
);

CREATE TABLE `savedLinks` (
  `id` INT,
  `userId` INT,
  `linkName` VARCHAR(100),
  `linkURL` VARCHAR(400),
  `linkNote` VARCHAR(100),
);

CREATE TABLE `BlogEntry` (
  `id` INT,
  `userId` INT,
  `entryTitle` VARCHAR(100),
  `entryExcerpt` VARCHAR(100),
  `entryBody` TEXT,
);

What I need:

if I search by these parameters:

keyword: naruto area51

keywordControl: AND

I expect to see results that match both naruto and area51 across all tables.

However, what I can do is filter results on the same table. I manage to display results like

• "Mister Naruto", who has an address of "Area51 lane..."

• Anyone who saved links that contain both "Naruto" and "Area51"

• Anyone who blogged about "Naruto running" and "Area51"

But I can't bring results like

• "Mister Naruto", who has blogged about "Area51"

• anyone who saved links about "Naruto", and blogged about "Area51"

Because those latter need to search across all tables. This is my laravel eloquent code.

$keyword=urldecode($request->input('keyword'));
$keywords=preg_split('/\s+/', $keyword, -1, PREG_SPLIT_NO_EMPTY);

$users=User::when(sizeof($keywords)>0,function($query) use ($keywords,$keywordControl) {
              $query->where(function ($query) use ($keywords,$keywordControl) {
                    foreach ($keywords as $keyword) {
                      if($keywordControl=='AND'){
                        $query->whereRaw("CONCAT(firstName,lastName,email,address) LIKE ?","%{$keyword}%");
                      }else{
                        $query->orWhereRaw("CONCAT(firstName,lastName,email,address) LIKE ?","%{$keyword}%");
                      }
                    }
                 });
            })
            ->when(sizeof($keywords)>0,function($query) use ($keywords,$keywordControl) {
              $query->whereHas('savedLinks', function($query) use ($keywords,$keywordControl){
                $query->where(function ($query) use ($keywords,$keywordControl) {
                    foreach ($keywords as $keyword) {
                      if($keywordControl=='AND'){
                        $query->whereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?","%{$keyword}%");
                      }else{
                        $query->orWhereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?","%{$keyword}%");
                      }
                    }
                 });
                });
            })
            ->when(sizeof($keywords)>0,function($query) use ($keywords,$keywordControl) {
              $query->whereHas('blogEntry', function($query) use ($keywords,$keywordControl){
                $query->where(function ($query) use ($keywords,$keywordControl) {
                    foreach ($keywords as $keyword) {
                      if($keywordControl=='AND'){
                        $query->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?","%{$keyword}%");
                      }else{
                        $query->orWhereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?","%{$keyword}%");
                      }
                    }
                 });
                });
            })
    ->get();

What I have in my mind is that, or if I should create another table and concat all those text in the same field for search purpose only. I don't know if it's a good idea or not. Please guide me, thanks!

via Chebli Mohamed