Sorting by aliases

[veganista]

Anyway of doing something like this?

$invoices = Invoice::select(
    DB::raw('(select sum(cost * quantity) from invoice_items where invoice_id = invoices.id) as total')
)->sortable('total')->paginate(24);

It currently produces the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoices.total' in 'order clause' (SQL: select (select sum(cost * quantity) from invoice_items where invoice_id = invoices.id) as total from `invoices` order by `invoices`.`total` asc limit 24 offset 0)

It's caused by the table name being added to the column here. I've tested by commenting out this line and it works but that's not the solution as it's the fix for #39.

Could it be possible to override this feature by specifying a field name when needed in the Model::$sortable config array?

public $sortable = [
    'total' => 'total',
    'created_at',
];

[Kyslik]

I will try to make it work. Thank you for suggestion with $sortable array.

Isn't it possible to use https://github.com/Kyslik/column-sortable#columnsortable-overloading-advanced ?

[veganista]

Yes, overloading for the total field works for my example above.

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

I think that this is probably the best solution rather than allowing the configuration of fields to be set.

public $sortable = [
    'total' => 'total',
    'balance' => 'balance',
    'anotherAliasedField' => 'anotherAliasedField',
    'created_at',
];

Doesn't look great as it's not obvious why you would be repeating the field name and i can't think of another use case for allowing that.

This is related to #24 as it would solve that guy's problem.

[Kyslik]

I think something like introducing new variable would be nice, maybe:

$sortableAliases = []

Or something similar, I will think about it and make it work.

---

WIP, aliases expected in new minor version

[Kyslik]

@veganista Hey, I was thinking about fixing this issue.

Anyway, #24 won't be fixed by this because OP uses accessor (manipulation with data after it is fetched) so there is no (virtual) column that package can sort by; in example below I show query with full_name and package could sort by it...

SELECT neededfield, CONCAT(firstname, ' ', lastname) as full_name ...

For that purpose there is that overloading functionality (which I use a lot in my projects), custom joins, custom selects etc.

---

I will try fix this soon.

[Kyslik]

@veganista try on dev-master, see README.md and please report back if everything OK.

[veganista]

Sorry for the delay in getting back on this, took me a while to get back on the project.

Works as expected and much more concise than overloading.

Thanks