sortable() on fields from HasOne relation not working on lens

[Ivana-C]

• Laravel Version: 8.83.18
• Nova Version: 4.11.1
• PHP Version: 8.0.18

Description:

I have a User resource with a HasOne relation. The lens (attached to User) displays fields for both resources, but relation fields generate a server error when sorted, because Nova is looking for them inside the User resource.

Is this actually possible to achieve? Or the underlying resource will always be the one that the lens is registered to, which means that it is not possible to sort by other resource fields?

[crynobone]

It would be useful to share the relevant code and relevant errors.

[Ivana-C]

User resource fields:

ID::make('ID', 'user_id')->sortable(),
Text::make('Email', 'email')->sortable()->rules('required', 'email', 'max:255'),
[...]
HasOne::make('UserWorkinfo', 'workinfo'),

Lens fields:

Text::make('Email', 'email')->sortable(),
Text::make('Job Title', 'workinfo.job_title')->sortable(),

Workinfo fields are displayed properly but trying to sort them generates a undefined table error, which mentions there is no [user table name].workinfo.job_title.
I updated the query to include the workinfo table but it didn't change the outcome.

Query:
return $request->withOrdering($request->withFilters(
      $query
      ->select('user.*')
      ->join('user_workinfo', 'user.user_id', 'user_workinfo.user_id')
      ->whereNotNull('user.user_id')
));

[juse-less]

I've been searching both issues and discussions for the past half hour of what I'm pretty sure Mior (crynobone) wrote within the past few months about this, but can't find it..
Nonetheless. In order to avoid the attribute being sorted from getting a table prefix, you can use $request->withoutTableOrderPrefix()->withOrdering().
So in your case, that'd be

return $request->withoutTableOrderPrefix()->withOrdering($request->withFilters(
      $query
      ->select('user.*')
      ->join('user_workinfo', 'user.user_id', 'user_workinfo.user_id')
      ->whereNotNull('_user.user_id')
));

Something I noticed, though -
Your example joins the user_workinfo table, but the lens field references workinfo.
I can't remember if it'll correctly pick up the model relation or not. If not, you'll need to reference the table name instead.

Also keep in mind that dot notation is more of a hack and not fully supported.
It'll work in lenses since you're only getting the attribute value, but in edits, the attribute won't be correctly set.
Just a heads up. 🙂

Double-check my code snippet as I wrote this on my phone.

[Ivana-C]

Updating the query like you suggested and renaming the relations to match the table they're referencing fixed the issue. Thank you very much!