column-sortable not working with withCount()

[ritterg]

This package seems not to work correctly with the new laravel function withCount().

I have models "Entry" and "Vote". One Entry can have many Votes, so in Entry

public function votes() {
    return $this->hasMany('App\Vote');
}

Entry::withCount('votes') returns the number of votes correctly in votes_count, but trying to sort by votes_count (via sortable) results in an error:

Unknown column 'entries.votes_count' in 'order clause'.

I was able to make it work by overloading the sort for "votes_count":

public function votesCountSortable($query, $direction) {
        return $query->orderBy('votes_count', $direction);
}

(simply making sure the name of the order column is only votes_count without a table name).

Note: the name of the function: votes_count in camel-case translate to votesCount.

[Kyslik]

Hi! Please try aliasing.

https://github.com/Kyslik/column-sortable#sortableas-aliasing

in your case it should be something like:

$sortableAs = ['votesCount'];

As I am looking at this right now I will improve this, you may try aliasing but if that renders any error just report it back here and stick with overloading.

I will look at this since 5.4 is right around the corner out already.

[ritterg]

I tried aliasing before but obviously did something wrong.

protected $sortableAs = ['votes_count'];

in the Entry model did the trick and is slightly easier than overloading.

Note: no camelcase for the variable name here: votesCount doesn't work, votes_count does.

Thanks!

[Kyslik]

I will update readme for L5.4, thanks!

[mauro-ni]

Hi, I'm on Laravel 5.5 and I have an issue.
I'm using withCount on a hasManyThrough association. Sorting by _count fields or attributes of the model defining the association is fine, but when sorting by attributes of other associated models then _count attributes are not set anymore.

[Kyslik]

Hey @maurosbu I do not think I can help you out (no time); only if you can reproduce this on L5.8. I will try to solve it & port the solution to the all versions of this package.

Make a fork of https://github.com/Kyslik/column-sortable-example and just make a PR with your reproduced bug.

[mauro-ni]

Hey @Kyslik, can you give a look at https://github.com/maurosbu/column-sortable-example/
If you sort by Address you loose counts.

Thanks,
Mauro

[stephaned-exatech]

Hi, the columns selected in the query are overwritten with select($parentTable.'.*') in the private function formJoin of Sortable.php

i think it must be done only if the columns is null

`private function formJoin($query, $parentTable, $relatedTable, $parentPrimaryKey, $relatedPrimaryKey)
{
$joinType = config('columnsortable.join_type', 'leftJoin');

    if ($query->getQuery()->columns === null) {
        $query->select($parentTable.'.*');
    }

    return $query->{$joinType}($relatedTable, $parentPrimaryKey, '=', $relatedPrimaryKey);
}`

what do you think ?

[wasxxm]

This should be incorporated in the code because any custom $query->select(...) doesn't work owing to this overriding.

if ($query->getQuery()->columns === null) {
    $query->select($parentTable.'.*');
}

[bessone]

I have the same problem, when sorting for a related table which may have nulls, the count is lost.

The fix proposed by @stephaned-exatech solves, after years can we consider it valid and do a PR?

    private function formJoin($query, $parentTable, $relatedTable, $parentPrimaryKey, $relatedPrimaryKey)
    {
        $joinType = config('columnsortable.join_type', 'leftJoin');

        if ($query->getQuery()->columns === null) {
            $query->select($parentTable.'.*');
        }

        return $query->{$joinType}($relatedTable, $parentPrimaryKey, '=', $relatedPrimaryKey);
    }

[ster]

@Kyslik can you please fix with the proposed solution?