MySQL error when ordering by join

[ionesculiviucristian]

Hi. I get the following error:

QLSTATE[42S22]: Column not found: 1054 Unknown column 'sort' in 'order clause' (SQL: select count(*) as aggregate from `cities` left join `counties` on `counties`.`id` = `cities`.`county_id` and `counties`.`deleted_at` is null where `county_id` = 2 and `cities`.`deleted_at` is null group by `cities`.`id` order by sort asc)

This happens on a very simple County->Cities tables relation.

Code running the query:

        $builder = City::query();

        $countyColumnIndex = 1;
        $cityColumnIndex   = 2;
        $countyFilterKey   = "columns.{$countyColumnIndex}.search.value";
        $cityFilterKey     = "columns.{$cityColumnIndex}.search.value";
        $orderKey          = 'order.0';
        $orderColumnKey    = 'order.0.column';
        $orderDirKey       = 'order.0.dir';

        if ($request->has($countyFilterKey) && ($county = (int) $request->input($countyFilterKey))) {
            $builder->where('county_id', $county);
        }

        if ($request->has($cityFilterKey) && ($city = $request->input($cityFilterKey))) {
            $builder->where('name', 'like', "%{$city}%");
        }

        if ($request->has($orderKey)) {
            switch ($request->input($orderColumnKey)) {
                case $countyColumnIndex:
                    $builder->orderByJoin('county.name', $request->input($orderDirKey));

                    break;
                case $cityColumnIndex:
                    $builder->orderBy('name', $request->input($orderDirKey));

                    break;
            }
        } else {
            $builder->orderBy('name', 'asc');
        }

        $filteredCount = $builder->count();

        $cities = $builder->offset((int) $request->get('start'))->limit((int) $request->get('length'))->get();

[fico7489]

yeah , this is a stupid "laravel bug" or at least "laravel strange behaveiour" for me.

this is not working in laravel :

City::select('something')
    ->orderBy('something', 'asc')
    ->count();

can you try workaround that @LuizFelippe suggested here :
laravel/framework#22883
?

change this line :
$filteredCount = $builder->count();
to
$filteredCount = $builder->getQuery()->getCountForPagination();

[ionesculiviucristian]

Many thanks. Seems to work fine. Didn't know about the bug.

[ionesculiviucristian]

Had to change some things but this is the working version:

        $builder = City::query();

        $countyColumnIndex = 1;
        $cityColumnIndex   = 2;
        $countyFilterKey   = "columns.{$countyColumnIndex}.search.value";
        $cityFilterKey     = "columns.{$cityColumnIndex}.search.value";
        $orderKey          = 'order.0';
        $orderColumnKey    = 'order.0.column';
        $orderDirKey       = 'order.0.dir';

        if ($request->has($countyFilterKey) && ($county = (int) $request->input($countyFilterKey))) {
            $builder->where('county_id', $county);
        }

        if ($request->has($cityFilterKey) && ($city = $request->input($cityFilterKey))) {
            $builder->where('cities.name', 'like', "%{$city}%");
        }

        if ($request->has($orderKey)) {
            switch ($request->input($orderColumnKey)) {
                case $countyColumnIndex:
                    $builder->orderByJoin('county.name', $request->input($orderDirKey));

                    break;
                case $cityColumnIndex:
                    $builder->orderBy('cities.name', $request->input($orderDirKey));

                    break;
                default:
                    $builder->orderBy('cities.name', 'asc');

                    break;
            }
        } else {
            $builder->orderBy('cities.name', 'asc');
        }

        $filteredCount = $builder->getQuery()->getCountForPagination();

        $cities = $builder->offset((int) $request->get('start'))->limit((int) $request->get('length'))->get();