include relationships in queries

[MartinP7r]

I think this might be beyond the scope of this project but I hope you don't mind me asking:

Would there be a way to dig down into relations for the table data via the allowedIncludes() method of QueryBuilder?

E.g. if I have a model with a belongsTo relation to User, I want to be able to display the user's name, not only his id.

So, a query like

QueryBuilder::for(SomeModel::class)
            ->allowedIncludes('user')
            ->defaultSort('id')
            ->allowedSorts(['id', 'user_id'])
            ->allowedFilters(['user_id', $globalSearch])
            ->paginate()
            ->withQueryString();

and probably some setting to have that include called by default?

Best regards, Martin

[neilgilmour]

There's some info about this on the Query Builder docs: https://spatie.be/docs/laravel-query-builder/v3/features/including-relationships#selecting-included-fields

[pascalbaljet]

The QueryBuilder also accepts an Eloquent Builder, so you could do something like this:

QueryBuilder::for(SomeModel::with('users'))

[zamblas]

Is there any example available?

On a users table, I'm trying to add a column with the user role. Got it to show using QueryBuilder::for(User::with('roles')) on the controller and on the .vue

<template #cell(roles)="{ item: user }">
    {{ user.roles.map(role => role.name).join(', ') }}
</template>

But when sorting that column it gives an error Unknown column 'roles' in 'order clause'.

How can I sort by relationship? I've tried adding 'roles', 'roles.name' to allowedSorts but with no success

[jbunning]

@zamblas 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);