Relationship columns doesn't work?

[darkons]

I tried my first table with relation columns and I can't get them to work properly.

// This column returns the entire provider_type object
column(key: 'provider_type', label: 'Type', canBeHidden: false)

// This column returns empty value
column(key: 'provider_type.name', label: 'Type', canBeHidden: false)

My query builder:

$providers = QueryBuilder::for(Provider::class)
            ->with(['providerType'])
            ->defaultSort('name')
            ->allowedSorts(['name', 'cif'])
            ->allowedFilters(['name', 'cif'])
            ->paginate()
            ->withQueryString();

What am I doing wrong?

Thank you!

[jbunning]

@darkons you can aggregate the relations columns in the query builder:

QueryBuilder::for(Model::class)
->withAggregate('relation', 'column')

Then use like this:

$table->column('relation_column', 'Displayed name', sortable: true, searchable: true);

[darkons]

Thank you @jbunning! Now the value is displayed correctly in the table.

However I can make the column searchable because:
Requested filter(s) provider_name are not allowed

but if I add the provider_name filter to query builder allowedFilters:
Column not found: 1054 Unknown column 'domains.provider_name' in 'where clause'

Tried with provider.name filter but still the same :(

[jbunning]

@darkons Ran into the same problems myself, I was able to find solutions but it got a bit ugly.

Already I replaced the query builder for Laravel's one instead of the Spatie package, then had to do things like this:

Model::query()
                ->select('joined.ambiguous as joined.ambiguous', 'joined.*', 'model.*')
                ->leftJoin('joined', 'joined.model_id', '=', 'model.id' )
                ->where(request()->filter)
                ->orderBy(request('sort') ? Str::remove('-', request('sort')) : 'model.id', Str::contains(request('sort'), '-') ? 'DESC' : 'ASC')
                ->paginate(request('perPage') ?? 20)
                ->appends(request()->except('page'))

Basically reporting searches, and sorting manually, adding aliases for ambiguous columns, etc.

It seems to be working now, even if it's a bit hectic.

Hope the package devs will find a nicer solution ;)

[darkons]

After some research I think I have found the reason why the table does not show nested values from relationships:

Example:

$users = QueryBuilder::for(User::class)
            ->defaultSort('name')
            ->allowedSorts(['name', 'email'])
            ->allowedFilters(['name', 'email', 'phone.number'])
            ->with('phone') // Eager load phone relation
            ->paginate()
            ->withQueryString();

        return Inertia::render('Users/Index', [
            'users' => $users,
        ])->table(function (InertiaTable $table) {
            $table
              ->defaultSort('name')
              ->column(key: 'name', searchable: true, sortable: true, canBeHidden: false)
              ->column(key: 'email', searchable: true, sortable: true)
              ->column(key: 'phone.number', searchable: true)
              ->column(label: 'Actions');

With the current version the phone.number column will display an empty value (undefined). This is because in Components/Table.vue the column key is not being searched deeply through the object:

<slot :name="`cell(${column.key})`" :item="item">
  {{ item[column.key] }}
</slot>

Using Lodash get method we can make it work:

<slot :name="`cell(${column.key})`" :item="item">
  {{ get(item, column.key) }}
</slot>

Example of how it works https://playcode.io/979205

It is actually a very simple fix. Hopefully @pascalbaljet can take a look at it and fix it :)

[SDIjeremy]

@darkons you can aggregate the relations columns in the query builder:

QueryBuilder::for(Model::class)
->withAggregate('relation', 'column')

Then use like this:

$table->column('relation_column', 'Displayed name', sortable: true, searchable: true);

Can you provide a less generic example, sorry I am having issues implementing this in my project.

[MaticSulc]

Any updates on this? I'd love to keep the source intact, although the @darkons's fix works like a charm.

[mbeckerle-xqueue]

For now, I also used aggregation and decided not to change the source of the table as our deployment process does not like manual interference. Instead I used a template and dumped the content, there but with the disadvantage, that neither sorting nor searching works very well...
I guess it is a good idea to think further about the proposal of @darkons but there might also some drawbacks I am not sure about, e.g. if you are going to use a custom template for a cell, event he name is a problem.
I try to explain with some example: when you are displaying football players and you also load a relation called "club" and you want to do something "fancy" like showing a font awesome globe in front of the name and link it somewhere, you probably try to do it like this:

->column(key: 'club.name', label: 'Club', searchable: true, sortable: true, hidden: false, canBeHidden: false)

And do some templating:
<template #cell(club.name)="{ item: element }"> <a href="whatever"><i class="fa-solid fa-globe fa-xl"></i>{{element.name}}</a> </template>

This works but the name club.name is actually invalid and you might see some warning like in my current VSCode: "'v-slot' directive doesn't support any modifier.eslint-plugin-vue"

@SDIjeremy
In the example above it could look like this: assume you have a class Player and a class Club. The class Player has a relation to a club, so something like:
public function club() { return $this->belongsTo(Club::class); }

Then you can load the relation and aggregate its name attribute:
QueryBuilder::for(Player::class) ->withAggregate('club', 'name')

You can then access the aggregated variable in your column but I failed to make sorting and filtering work...
$table->column('club_name', 'Name of the Club of the Player :)', sortable: true, searchable: true);

I assume it is a generic problem: spatie/laravel-query-builder#91 however, I did not make sorting work, yet

[MaticSulc]

I've been looking at this issue, for now I've just ended up restructuring our data.
Any solution would be greatly appreciated, I'm not sure how this is not a wider issue.

[SDIjeremy]

@MaticSulc for sorting I had to just restructure my data as well, I think it's probably for the better anyways.

[musiwei]

If you are still wondering how to filter a relationship, here is the most complicated case, then One-to-many is easy.

Many-to-many

Example: users-roles relationship, filter by role name.

UserService.php

$roleSearch = AllowedFilter::callback('roles_name', static function ($query, $value) {
            $query->where(function ($query) use ($value) {
                Collection::wrap($value)->each(function ($value) use ($query) {
                    $query->orwhereHas('roles', function ($subquery) use ($value) {
                        $subquery->where('name', 'LIKE', "%{$value}%");
                    });
                });
            });
        });

You definitely want to put it in the global filter.

$globalSearch = AllowedFilter::callback('global', static function ($query, $value) {
            $query->where(function ($query) use ($value) {
                Collection::wrap($value)->each(function ($value) use ($query) {
                    // Copy: Begin
                    $query->orwhereHas('roles', function ($subquery) use ($value) {
                        $subquery->where('name', 'LIKE', "%{$value}%");
                    });
                    // Copy: End
                    $query
                        ->orWhere('first_name', 'LIKE', "%{$value}%")
                        ->orWhere('last_name', 'LIKE', "%{$value}%")
                        ->orWhere('email', 'LIKE', "%{$value}%")
                        // Practically you want to do this so your search is more human-friendly. 
                        ->orWhere(DB::raw("CONCAT(first_name, ' ', last_name)"), 'LIKE', "%$value%");
                });
            });
        });

Then add the custom filter:

$users = QueryBuilder::for(User::class)
            // This has nothing to do with the filter, just to provide the role names, see the end of this comment
            ->with('roles:id,name')
            ->defaultSort('first_name')
            ->allowedSorts(['first_name', 'last_name', 'email'])
            ->allowedFilters(['first_name', 'last_name', 'email', $roleSearch, $globalSearch])
            // A lil sugar, in case your pagination won't work out of the box
            // It was not your fault, the example provided was wrong. 
            ->paginate($queryParams['perPage'] ?? 15) 
            ->appends($queryParams);

UserController.php

return Inertia::render('User/Index', ['users' => $users])->table(function (InertiaTable $table) {
        ...
        $table->column(key: 'roles_name', label: 'Role', searchable: true);
        ...
});

You don't sort a many-to-many relationship for obvious reason. Even if you want, it's still achievable, just define your rule usingAllowSort callback and pass it into ->allowedSorts(...)

This way you can get rid of withAggregate, and you should, as It doesn't make sense for a table designed to sort/filter to just get a value.

Below is how you can display all the role names in Vue.

User/Index.vue

<Table :resource="users">
                ...
                <template #cell(roles_name)="{ item: user }">
                    <div class="flex space-x-1">
                        <div v-for="role in user.roles">
                            <span>{{ role.name }}</span>
                        </div>
                    </div>
                </template>
                ...
</Table>

[MaticSulc]

Thanks @musiwei - it works as intended, although I can't get my one to many relationships to show in the table.
By doing key: 'owner' it shows the full JSON. Then doing owner_name or owner.name doesn't do anything - although I see the name attribute in the JSON.
Do I really have to specify all of them manually in Vue?

[musiwei]

Thanks @musiwei - it works as intended, although I can't get my one to many relationships to show in the table. By doing key: 'owner' it shows the full JSON. Then doing owner_name or owner.name doesn't do anything - although I see the name attribute in the JSON. Do I really have to specify all of them manually in Vue?

Glad that I helped. Could you please paste your code? It's more intuitive and others can learn from it too.

[MaticSulc]

Basically, I have a "Ticket", that can have an "Owner". The owner has props like name, address ...

$ownerSearch = AllowedFilter::callback('owner_name', static function ($query, $value) {
    $query->where(function ($query) use ($value) {
        Collection::wrap($value)->each(function ($value) use ($query) {
            $query->orwhereHas('owner', function ($subquery) use ($value) {
                $subquery->where('name', 'LIKE', "%{$value}%");
            });
        });
    });
});

$globalSearch = AllowedFilter::callback('global', static function ($query, $value) {
    $query->where(function ($query) use ($value) {
        Collection::wrap($value)->each(function ($value) use ($query) {
            $query->orwhereHas('owner', function ($subquery) use ($value) {
                $subquery->where('name', 'LIKE', "%{$value}%");
            });
            $query
                ->orWhere('number', 'LIKE', "%{$value}%");
        });
    });
});

//inside QueryBuilder
->with('owner:id,name')
->allowedFilters(['number', $globalSearch, $ownerSearch])
//inside Table call
->column(key: 'owner_name', label: 'Owner', searchable: true, sortable: false, canBeHidden: false)

It works by doing the folowind in Vue, but I'd expect a better solution here.

<template #cell(owner_name)="{ item: order }">
    {{ order.owner.name }}
</template>

[musiwei]

Basically, I have a "Ticket", that can have an "Owner". The owner has props like name, address ...

$ownerSearch = AllowedFilter::callback('owner_name', static function ($query, $value) {
    $query->where(function ($query) use ($value) {
        Collection::wrap($value)->each(function ($value) use ($query) {
            $query->orwhereHas('owner', function ($subquery) use ($value) {
                $subquery->where('name', 'LIKE', "%{$value}%");
            });
        });
    });
});

$globalSearch = AllowedFilter::callback('global', static function ($query, $value) {
    $query->where(function ($query) use ($value) {
        Collection::wrap($value)->each(function ($value) use ($query) {
            $query->orwhereHas('owner', function ($subquery) use ($value) {
                $subquery->where('name', 'LIKE', "%{$value}%");
            });
            $query
                ->orWhere('number', 'LIKE', "%{$value}%");
        });
    });
});

//inside QueryBuilder
->with('owner:id,name')
->allowedFilters(['number', $globalSearch, $ownerSearch])
//inside Table call
->column(key: 'owner_name', label: 'Owner', searchable: true, sortable: false, canBeHidden: false)

It works by doing the folowind in Vue, but I'd expect a better solution here.

<template #cell(owner_name)="{ item: order }">
    {{ order.owner.name }}
</template>

What do you mean by 'a better solution'? Isn't this working as intended? What is a better solution from your perspective?

[MaticSulc]

@musiwei By simply doing owner_name inside the column key, I'd expect the data to get extracted - not that I have to create a slot and extract it myself.