How to search relationship (hasMany) column

[jechazelle]

Hi,

Currently I have two tables:

books (with translations relations: HasMany): uuid...

public function translations(): HasMany
    {
        return $this->hasMany(BookTranslation::class, 'book_uuid');
    }

books_translations: uuid, name, book_uuid, language_uuid...

I have a BookResource like this:

public static function table(Table $table): Table
    {
        return $table->columns([
            TextColumn::make('books')
                ->getStateUsing(fn ($record) => self::getFirstTranslationContent($record))
                ->searchable(['name']),
        ....
   }

public static function getFirstTranslationContent($record)
    {
        return $record->translations()->first()->name;
    }

How can I set a search on the name please ?

I tried this code but I have an error :

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

select count(*) as aggregate from `books` where (`position` like %e% or `name` like %e%) and `books`.`deleted_at` is null

I tried also this, but I have no result on the list when I search:

->searchable(query: function (Builder $query, string $search): Builder {
                    return $query
                        ->with(['translations' => function ($query) use ($search) {
                            $query->where('translations.name', 'like', "%{$search}%");
                    }]);
                }),

Thank you so much for your help!

[jechazelle]

@danharrin is that something that is possible?

[danharrin]

1. Please don't @ me, there are plenty of other people here who can help you
2. Your current solution has an N+1 query problem as you are fetching the first translation for each row in a separate query
3. Instead, create a HasOne of many relationshiphttps://laravel.com/docs/10.x/eloquent-relationships#has-one-of-many and use that as the relationship. Then you do not need to do anything custom

[jechazelle]

I understand, thank you for your help and your answer!