How to sort a computed Lenses column

[beshoo]

Hi

I have this Lense query

    public static function query(LensRequest $request, $query)
    {
        return $request->withOrdering($request->withFilters(
            $query->leftJoin('attendeds', function ($join) {
                $join->on('attendeds.user_id', '=', 'users.id')
                    ->whereNotNull('attendeds.presence')
                    ->whereNotNull('attendeds.leave');
            })
                ->select('users.id', 'users.email', 'users.name', 'users.phone', 'users.password', 'users.time_zone',
                    DB::raw('SUM(CASE WHEN attendeds.presence IS NOT NULL AND attendeds.leave IS NOT NULL THEN TIME_TO_SEC(TIMEDIFF(attendeds.leave, attendeds.presence)) ELSE 0 END) as total_attended'))
                ->groupBy('users.id', 'users.email', 'users.name', 'users.phone', 'users.password', 'users.time_zone')
    ));
    }

I am having an issue with sorting a computed column in my code. Specifically, I have the following line of code:

Text::make('Working Hours', 'total_attended')->sortable()->textAlign('center')

However, when I attempt to sort, I receive an error message that states:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.total_attended' in 'order clause ......... order by users.total_attended asc limit 26 offset 0)"

Actually there is no column named total_attended it is computed column.
I have tested running the SQL statement "ORDER BY total_attended ASC" and it works perfectly fine.

the ROW SQL:

SELECT users.id, users.email, users.name, users.phone, users.password, users.time_zone, 
SUM(CASE WHEN attendeds.presence IS NOT NULL AND attendeds.leave IS NOT NULL 
THEN TIME_TO_SEC(TIMEDIFF(attendeds.leave, attendeds.presence)) ELSE 0 END) AS total_attended
FROM users
LEFT JOIN attendeds ON attendeds.user_id = users.id AND attendeds.presence IS NOT NULL AND attendeds.leave IS NOT NULL
GROUP BY users.id, users.email, users.name, users.phone, users.password, users.time_zone
ORDER BY total_attended ASC
LIMIT 26 OFFSET 0

How can I properly sort this computed column?

[crynobone]

See https://github.com/laravel/nova-docs/pull/539

[beshoo]

Lenses query

public static function query(LensRequest $request, $query)
  {

      return $request->withOrdering($request->withFilters(
          $query->leftJoin('attendeds', function ($join) {
              $join->on('attendeds.user_id', '=', 'users.id')
                  ->whereNotNull('attendeds.presence')
                  ->whereNotNull('attendeds.leave');
          })
              ->select('users.id', 'users.email', 'users.name', 'users.phone', 'users.password', 'users.time_zone',
                  DB::raw('SUM(CASE WHEN attendeds.presence IS NOT NULL AND attendeds.leave IS NOT NULL THEN TIME_TO_SEC(TIMEDIFF(attendeds.leave, attendeds.presence)) ELSE 0 END) as total_attended'))
              ->groupBy('users.id', 'users.email', 'users.name', 'users.phone', 'users.password', 'users.time_zone')
  ),fn ($query) => $query->orderBy('total_attended', 'desc'));
  }

[beshoo]

@crynobone Do you think this is a bug?

[jimrubenstein]

Sorry to resurrect;

Do you recall if you ever solved this?