Search in parent relationship with the database driver

Question

Let's say you have this relationship: users x cats. Each user can have many cats (a "one-to-many" relationship):

class Cat extends Model 
{
    public function user() 
    {
        return $this->belongsTo(User::class);
    }
}

Both models (users and cats) have a name field.

Let's say we want to get all cats with bob in their names, using Laravel's Scout.

The standard solution is to add this to the Cat.php model:

// Cat.php
use Searchable;

/**
  * Get the indexable data array for the model.
  *
  * @return array
  */
public function toSearchableArray()
{
    return [
        'name' => $this->name,
    ];
}

And we search with Cat::search('bob')->get().

The problem

The above solution works well, but what if we want to search in the relationship's fields?

What if you want to get cats owned by people with bob in their names?

If you add this to the "Cat" model:

// Cat.php
use Searchable;

/**
  * Get the indexable data array for the model.
  *
  * @return array
  */
public function toSearchableArray()
{
    return [
        'name' => $this->name,
        'users.name' => '', // no need to return `$this->user->name` as the database engine only uses the array keys
    ];
}

It won't work. You will get this exception when running Cat::search('bob')->get():

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.name' in 'where clause'

SQL: select `cats`.* from `cats` where (`cats`.`name` like %bob% or `users`.`name` like %bob%)

Clearly, the SQL is missing the users table. But how to add it? Doing a Cat::join(...)->search('bob') will throw an exception, same for Cat::search(...)->join(...).

The question is: How to search in the parent attributes? And by "parent" I mean the "belongsTo" model.

score 0 · Answer 1 · edited Dec 18 '22 at 01:29

The query method allows for modifing the search query. Use it to inject a join clause:

Cat::search('bob')->query(function ($builder) {
    $builder->select('cats.*')->join('users', 'cats.user_id', '=', 'users.id');
})->get();

This generates the proper query:

SELECT `cats`.*
FROM `cats` 
INNER JOIN `users` on `cats`.`genre_id` = `users`.`id` 
WHERE (`cats`.`name` LIKE '%bob%' or `users`.`name` LIKE '%bob%') 
ORDER BY `id` desc

EDIT: Automatically adds the JOIN clause to all searches:

If you want to search with just Cat::search('bob')->get(), without having to write ->join(...) on every call:

// Cat.php
/**
 * Overrides the "search" method to inject a `join` to the relationships.
 */
use Searchable {
    Searchable::search as parentSearch;
}

/**
 * Perform a search against the model's indexed data.
 *
 * @param  string  $query
 * @param  \Closure  $callback
 * @return \Laravel\Scout\Builder
 */
public static function search($query = '', $callback = null)
{
    return static::parentSearch($query, $callback)->query(function ($builder) {
        $builder->select('cats.*')->join('users', 'cats.user_id', '=', 'users.id');
    });
}

Search in parent relationship with the database driver

The problem

1 Answers1