Trying to sort relationship column in index throws SQL-error

[carnevalle]

Nova version: v1.0.5

I have declared the following field inside a resource called CampaignCode
BelongsTo::make('user')->sortable()->searchable()

The campaign_codes table has the following migration
$table->integer('user_id')->unsigned()->nullable(); $table->foreign('user_id') ->references('id')->on('users') ->onDelete('cascade');

The index looks like this

Expected behavior
Clicking on sort would sort the list after the $title property of the User resource.

Actual behavior
Clicking on sort results in the following error

---

If the reason is that sorting is currently not supported for Relationships I would expect it not to enable sorting.

[v1nk0]

@carnevalle it seems that the query to select the items to display on the index-page is a Query-Builder query that does not load/join any of the relations (also when this relation is within the $with array of the resource). Therefore, it's not possible to sort by a relationship column, although (like you said) it's possible to add ->sortable() and the column shows sorting icons in the UI.

I hope for a swift solution for this problem too.
For the time being I implemented a work-around that I described here: #157 (comment)

(frankly it does load the relationship(s) when the same index-list is shown on the detail-page of a resource that has for example a hasMany relationship with the resource in question. Therefore it's important to use an alias in the custom join that the work-around useses)

[flashadvocate]

Why can't it just default to sorting by the id? If I have a table column of rank_id and that provides a relationship to Rank, I don't need to eager load anything simply to sort by the id in the table...

[v1nk0]

@flashadvocate it's about the field that is visible for the user. Why should I show the (in your case) rank_id field and make that sortable for the user. The user has no idea what this id is and sorting on an id makes no sense in your case (the id says nothing about the rank)

[flashadvocate]

@v1nk0 Valid point, title should be the default. If not, an eager load would be nice default behavior (so a display_order column could be used). At any rate, it seems silly not to be able to sort.

[davidhemphill]

Currently Nova doesn't support sorting on relationships.

[mateusgalasso]

Some solution?

[gjsman]

Any solution?

[mikebronner]

Commenting here to get notified on this issue as well. This is quite important. I guess the work-around is to show a text field on the index, and the relationship in the detail view. The problem with that is how to pull the display value from the relationship. A possible work-around might be something like this:

    public function fields(Request $request)
    {
        return [
            Text::make("User", "user_id")
                ->resolveUsing(function ($userId) {
                    return (new User)->find($userId)->name ?? "";
                })
                ->onlyOnIndex()
                ->sortable(),
            BelongsTo::make("User")
                ->hideFromIndex(),
        ];
    }

[bernhardh]

@mikebronner: Why do you use

return (new User)->find($userId)->name ?? "";

and not just

return $this->user->name ?? "";

The problem with this solution is, that your usernames aren't linked with the resource. Since there is atm no method to generate the correct url with nova (see #345) here is my hacky solution, which of course only sorts after ID's and not after the real value:

return [
    BelongsTo::make("User", "user", User::class)->searchable()->hideFromIndex(),
    Text::make("User", "user_id", function (){
        return "<a href='/nova/resources/" . $this->user->getTable() . "/" . $this->user->getRouteKey() . "' class='no-underline dim text-primary font-bold'>" . $this->user->name . '</a>';
    })->asHtml()->sortable()->onlyOnIndex()
];

The fun part with nova is, that you can even search for the name of the user (searchable), even if its hiden from index.

[mikebronner]

@bernhardh Good point .... I didn't remember that I can access the relationship directly in the Resource model. :) I like your solution.

[flxsource]

Overriding the indexQuery on the resource also works.

public static function indexQuery(NovaRequest $request, $query)
{
    $resourceTable = 'parent_model_table_name';

    $query->select("{$resourceTable}.*");
    $query->addSelect('u.name as userName');
    $query->join('users as u', "{$resourceTable}.user_id", '=', 'u.id');

    $orderBy = $request->get('orderBy');

    if ($orderBy == 'user') {
        $query->getQuery()->orders = null;
        $query->orderBy('userName', $request->get('orderByDirection'));
    } else {
        $query->when(empty($request->get('orderBy')), function (Builder $q) use ($resourceTable) {
            $q->getQuery()->orders = null;

            return $q->orderBy("{$resourceTable}.name");
        });
    }

    return $query;
}

You have to manually add each sortable relation, but it works.

[endihunter]

As a good workaround for this could be accepting a closure as an argument for sortable method:
here is an example how admin architect handles it:

BelongsTo::make('User')->sortable(function($query, $sortBy, $sortDirection) {
    return $query->join('users', function ($join) {
        $join->on('users.id', '=', 'products.user_id');
    })->orderBy("products.{$element}", $direction);
});

also for more complex cases you can extend your indexQuery method

[infinitodk]

What's the point of paying for Nova @taylorotwell if we're missing basic functionality as sorting on relationships. One would expect Nova to at least disable sorting. Also, while we're at it, where's the inline form for editing hasMany relationships or displaying checkboxes / radios in the edit form.

When are we to expect a release supporting this basic functionality? Nova is a fine tool but currently with to many issues to ship to production for customers. An alternative is that you guys sends an e-mail to my customer and explains to them why they can't sort a table in a product which they paid for.

[jszobody]

I solved it by first overriding indexQuery like this, in my User resource:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company');
}

This ensures that my companies table is always joined, and that the company name is aliased as company in the query.

Now I can add the BelongsTo relationship, and just make it sortable.

BelongsTo::make('Company')->sortable(),

This gives me a Company column in my User index, and I can click on it to sort. Nova is sorting on a company field, which exists due to my join/alias, and effectively sorts my list of users by company name.

This could be abstracted out to a pretty simple trait, with just a class parameter that specifies the relationship table/attribute to join/alias.

[rostockahoi]

@infinitodk It is your responsibility to evaluate a third party software solution before you sell it to your client. If there are missing "must-have" features you should identify them and walk away to another solution.

[infinitodk]

@infinitodk It is your responsibility to evaluate a third party software solution before you sell it to your client. If there are missing "must-have" features you should identify them and walk away to another solution.

That's just stupid. https://nova.laravel.com/docs/1.0/resources/relationships.html it clearly states that "In addition to the variety of fields we've already discussed, Nova has full support for all of Laravel's relationships. Once you add relationship fields to your Nova resources, you'll start to experience the full power of the Nova dashboard."

There is also no evalutation-product available, so we'll have to pay to evaluate the software, since the documentation is clearly wrong.

[barnomics]

Not to pile on... I love Nova and everything, but this does seem like fairly necessary functionality for Nova. I don't know whether or not it would be easy to implement, but it should be a core piece of functionality.

[itomicspaceman]

Yes, this would be very handy indeed.

[pleone]

I'm getting bored.
The feeling is that i paid a license for a prototype instead for a complete product.
Customer service is always "we don't support that, we don't plan to implement that feature" or just ignoring the problem. Or, even worst, pretending is already fixed from long time but they actually just updated the documentation seconds before replying on issues ( #601 ).
So bad, so so so bad. Maybe that's the reason there is no trial available?
@taylorotwell @jbrooksuk @davidhemphill

[infinitodk]

@pleone - Could not have said this any better. The ignorance is strong within the Nova team.

[pleone]

Just to let everyone aware of: as a pro license customer, i have email support ( stated here https://nova.laravel.com/faq#difference-between-licenses ).
Wrote an email asking for clarifications about 3 hours ago. Will let you know when or IF i will have a reply.

[davidhemphill]

We would be glad to look at another Pull Request to add this. We already had one that didn't meet our requirements laravel/nova#726. Otherwise this feature will be prioritized according to our standard practice. 👍

Thanks!

[mikebronner]

@davidhemphill I think many of us would be glad to offer to provide PRs, but the code-base is locked, and we don't have access to it. At least I am unable to access it, as well as the link to the one you posted above. Is this something that can be addressed?

[gjsman]

@mikebronner Hi,

I have access to the Nova source code. Steps: login to nova.laravel.com, click your name in the corner, and choose Account Settings. Choose GitHub, "connect to GitHub." You will be granted access to the private repo.

[pleone]

Submitting PRs means free licenses? Otherwise i don't really see the point of asking PRs for a closed repo.

[davidhemphill]

@mikebronner You can connect your Nova account to the private repository here: https://nova.laravel.com/settings#github

@pleone Don't do it then? No one is asking for them and no one says you have to submit them. Submitting PRs is a good way to add visibility to issues, gives folks a way to practice and contribute to a product they love, and is just generally helpful. Your hostile tone in this thread is unwarranted.

[mikebronner]

@davidhemphill Thanks ... I am now connected.

[pleone]

Any updates on this?

[OliverZiegler]

Thanks @jszobody

I solved it by first overriding indexQuery like this, in my User resource:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company');
}

This ensures that my companies table is always joined, and that the company name is aliased as company in the query.

Now I can add the BelongsTo relationship, and just make it sortable.

BelongsTo::make('Company')->sortable(),

This gives me a Company column in my User index, and I can click on it to sort. Nova is sorting on a company field, which exists due to my join/alias, and effectively sorts my list of users by company name.

This could be abstracted out to a pretty simple trait, with just a class parameter that specifies the relationship table/attribute to join/alias.

I encountered a small problem with this solution.

When trying to access the relation in the model it sometimes (eg. in resolving title attribute) returns just the column value we added in the index query (as laravel prefers attibutes over relations when attribute is set with same name).

To get rid of this (and by inspecting the CR https://github.com/laravel/nova/pull/726 @davidhemphill linked). I came to the following working solution:

• Use some unused alias in the index query
• overwrite the sortableUriKey for the BelongsTo field

Updated Code:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company_name');
}

BelongsTo::make('Company')
    ->sortable()
    ->withMeta(['sortableUriKey' => 'company_name']),

[ahmadrio]

Overriding the indexQuery on the resource also works.

public static function indexQuery(NovaRequest $request, $query)
{
    $resourceTable = 'parent_model_table_name';

    $query->select("{$resourceTable}.*");
    $query->addSelect('u.name as userName');
    $query->join('users as u', "{$resourceTable}.user_id", '=', 'u.id');

    $orderBy = $request->get('orderBy');

    if ($orderBy == 'user') {
        $query->getQuery()->orders = null;
        $query->orderBy('userName', $request->get('orderByDirection'));
    } else {
        $query->when(empty($request->get('orderBy')), function (Builder $q) use ($resourceTable) {
            $q->getQuery()->orders = null;

            return $q->orderBy("{$resourceTable}.name");
        });
    }

    return $query;
}

You have to manually add each sortable relation, but it works.

Thanks it works for order by relation column

[cwilby]

@mikebronner Hi,

I have access to the Nova source code. Steps: login to nova.laravel.com, click your name in the corner, and choose Account Settings. Choose GitHub, "connect to GitHub." You will be granted access to the private repo.

This is no longer supported.

[michapixel]

Thanks @jszobody

I solved it by first overriding indexQuery like this, in my User resource:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company');
}

This ensures that my companies table is always joined, and that the company name is aliased as company in the query.
Now I can add the BelongsTo relationship, and just make it sortable.

BelongsTo::make('Company')->sortable(),

This gives me a Company column in my User index, and I can click on it to sort. Nova is sorting on a company field, which exists due to my join/alias, and effectively sorts my list of users by company name.
This could be abstracted out to a pretty simple trait, with just a class parameter that specifies the relationship table/attribute to join/alias.

I encountered a small problem with this solution.

When trying to access the relation in the model it sometimes (eg. in resolving title attribute) returns just the column value we added in the index query (as laravel prefers attibutes over relations when attribute is set with same name).

To get rid of this (and by inspecting the CR https://github.com/laravel/nova/pull/726 @davidhemphill linked). I came to the following working solution:

* Use some unused alias in the index query

* overwrite the `sortableUriKey`  for the `BelongsTo` field

Updated Code:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company_name');
}

BelongsTo::make('Company')
    ->sortable()
    ->withMeta(['sortableUriKey' => 'company_name']),

i might be missing some hidden detail, but this is not working on my nova install.

    {
        return $query
            ->join('productcategories', 'productcategory_id', '=', 'productcategories.id')
            ->select('products.*', 'productcategories.title as productcategory_name')
        ;
    }```
and
```BelongsTo::make(__('Productcategory'), 'productcategory', Productcategory::class)
                ->rules('required')
                ->updateRules('sometimes') // index-list-page returns error otherwise
                ->searchable()
                ->sortable() // gives sql error, since this is a "virtual" field, so we need:
                ->withMeta(['sortableUriKey' => 'productcategory_name']) // stil gives error on the join, since 
            ,```


but again i get the SQL Error:

```Column not found: 1054 Unknown column 'productcategory' in 'order clause' (SQL: select `products`.*, `productcategories`.`title` as `productcategory_name` from `products` inner join `productcategories` on `productcategory_id` = `productcategories`.`id` order by `productcategory` asc limit 26 offset 0)```

so the orderby statement should be "productcategory_name" but howe can i influence that? Or in other words: what did i do wrong?

MYSQL: 5.7.18
LARAVEL: 5.8.38
NOVA: 2.0.5 (i know, iknow :)

[michapixel]

Thanks @jszobody

I solved it by first overriding indexQuery like this, in my User resource:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company');
}

This ensures that my companies table is always joined, and that the company name is aliased as company in the query.
Now I can add the BelongsTo relationship, and just make it sortable.

BelongsTo::make('Company')->sortable(),

This gives me a Company column in my User index, and I can click on it to sort. Nova is sorting on a company field, which exists due to my join/alias, and effectively sorts my list of users by company name.
This could be abstracted out to a pretty simple trait, with just a class parameter that specifies the relationship table/attribute to join/alias.

I encountered a small problem with this solution.
When trying to access the relation in the model it sometimes (eg. in resolving title attribute) returns just the column value we added in the index query (as laravel prefers attibutes over relations when attribute is set with same name).
To get rid of this (and by inspecting the CR https://github.com/laravel/nova/pull/726 @davidhemphill linked). I came to the following working solution:

* Use some unused alias in the index query

* overwrite the `sortableUriKey`  for the `BelongsTo` field

Updated Code:

public static function indexQuery(NovaRequest $request, $query)
{
    return $query
        ->join('companies', 'company_id', '=', 'companies.id')
        ->select('users.*', 'companies.name as company_name');
}

BelongsTo::make('Company')
    ->sortable()
    ->withMeta(['sortableUriKey' => 'company_name']),

i might be missing some hidden detail, but this is not working on my nova install.

    public static function indexQuery(NovaRequest $request, $query)
   {
       return $query
           ->join('productcategories', 'productcategory_id', '=', 'productcategories.id')
           ->select('products.*', 'productcategories.title as productcategory_name')
       ;
   }

and

BelongsTo::make(__('Productcategory'), 'productcategory', Productcategory::class)
                 ->rules('required')
                 ->updateRules('sometimes') // index-list-page returns error otherwise
                 ->searchable()
                 ->sortable() // gives sql error, since this is a "virtual" field, so we need:
                 ->withMeta(['sortableUriKey' => 'productcategory_name']) 
 ,```
 

 but again i get the SQL Error:
 

Column not found: 1054 Unknown column 'productcategory' in 'order clause' (SQL: select products.*, productcategories.title as productcategory_name from products inner join productcategories on productcategory_id = productcategories.id order by productcategory asc limit 26 offset 0)

 
 so the orderby statement should be "productcategory_name" but howe can i influence that? Or in other words: what did i do wrong?
 
 MYSQL: 5.7.18
 LARAVEL: 5.8.38
 NOVA: 2.0.5 (i know, iknow :)

[DanielSpindler]

why was this issue closed even though theres no fix to it?