Hi, I am trying to sort an alphanumeric ID field on “LENGTH(field), field” so that it sorts correctly.
If you sort by default it sorts: 1,10,11,12 … 99, A, B, C, D, …
But I want it to sort 1,2,3 …9,A,B, … Z, 10, 11, 12, … 1Z, 20, …
Hope that makes sense. The sort I want is easy to do in SQL so I tried to rebuild it in the sprunje.
In my sprunje I have declared the custom sort like this:
protected function sortUniqueId($query, $direction)
{
$query->orderByRaw('LENGTH("unique_id")', $direction)->orderBy('unique_id', $direction);
return $this;
}
And this generates a Whoops page with the following error msg:
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (SQL: select “medias”.* from “media”.“medias” order by LENGTH(“unique_id”), “unique_id” asc)
But when I paste this query into PostgreSQL it works fine.
So my question is: Is this a bug or am I doing it wrong?
If I comment out the sprunje’s sortUniqueId function then it works but sorts by default and therefor not the way I want it to sort.
I have tried a few different approaches (quotes or no quotes, both sorts in the orderByRaw statement, …) but I always get the same error and I always get a correct query in the error report.
Thanks,
Bavo
This is running on Ubuntu 16.04LTS, PostgreSQL and UserForsting 4.1