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

Question

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!

Answer 1

Did you define relationships for User, SavedLinks and Blogs? For this query, it's necessary that User has the correct relationships with the models in question:

# User model
public function savedLinks()
{
    return $this->hasMany(SavedLink::class, 'userId', 'id');
}

public function blogEntries()
{
    return $this->hasMany(BlogEntry::class, 'userId', 'id');
}

All your when(...) conditions are the same. This query could be rewritten to take that into account.

Also, whereRaw() accepts 3 parameters. If you check it's definition, you'll see the following:

/**
 * Add a raw where clause to the query.
 *
 * @param  string  $sql
 * @param  mixed   $bindings
 * @param  string  $boolean
 * @return $this
 */
public function whereRaw($sql, $bindings = [], $boolean = 'and')
{
    $this->wheres[] = ['type' => 'raw', 'sql' => $sql, 'boolean' => $boolean];
    $this->addBinding((array) $bindings, 'where');
    return $this;
}

/**
 * Add a raw or where clause to the query.
 *
 * @param  string  $sql
 * @param  mixed   $bindings
 * @return \Illuminate\Database\Query\Builder|static
 */
public function orWhereRaw($sql, $bindings = [])
{
    return $this->whereRaw($sql, $bindings, 'or');
}

It also seems to me you should be using orWhereHas. You could use empty() instead of sizeof() and unless() (it's just the opposite function) instead of when() to make things more readable too. With that in mind, your query can already be reduced to this, assumming $keywordControl is either AND or OR:

$users = User::unless(empty($keywords), function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(firstName,lastName,email,address) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->orWhereHas('savedLinks', function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->orWhereHas('blogEntry', function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->get();

I don't know if concatenating things with whereRaw and then comparing that is faster than using where clauses for each attribute though.

# Example 
->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%", $keywordControl);
# vs
->where([
    ['entryTitle', 'like', "%{keyword}%", $keywordControl],
    ['entryExcerpt', 'like', "%{keyword}%", $keywordControl],
    ['entryBody', 'like', "%{keyword}%", $keywordControl],
])
# If someone could comment on this I'd be grateful.