Not so complex sort is reporting an error although the query it generates is correct


#1

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


#2

From what I’m reading regarding Eloquent, I believe your use of orderByRaw might be wrong. Try :

$query->orderByRaw('LENGTH(unique_id) ' . $direction)->orderBy('unique_id', $direction);

or even :

$query->orderBy('LENGTH(unique_id)', $direction)->orderBy('unique_id', $direction);

See :


#3

Hi Malou,
thanks for the quick reply but I had tried all of those already and had visited stackoverflow as well. Like I said, the query it generates is actually correct, it is simply UserFrosting generating an exception because it doesn’t think the query is correct (or that is what it looks like anyway) and it doesn’t continue, but if it would continue it would return the data correctly. I have attached a screenshot, it is definitely not a syntax error in the Laravel/Eloquent query builder code.
Bavo


#4

Hi Malou,
Sorry, I missed the . vs , in your suggested fix.
This one seems to work:
$query->orderByRaw(‘LENGTH(unique_id)’. $direction)->orderBy(‘unique_id’, $direction);

Thanks heaps, you’ve saved me a lot of time.
Bavo


#5

:+1: