Paginated Order By Raw Bindings Wrong

[sarahkemp]

I have a query like:

$query->where(function ($qu) use ($name) {
    $qu->where('upper(name)', 'like', strtoupper(substr($name, 0, 20)).'%')
       ->orWhere('upper(alpha)', 'like', strtoupper(substr($name, 0, 12)).'%')
       ->orderByRaw('case 
                        when phone like ? then 15
                        when phone like ? then 10
                        else 0
                     end desc', [
                        substr($phone, 0, 6).'%', 
                        substr($phone, 0, 3).'%'
                    ]);
});

This happens to anger DB2 because the phone field is numeric and the $name variable is a string, but the ultimate problem is that the OVER parameters being added to the select statement by DB2Grammar.php's compileOver steal the bindings that should have gone to the where parameters. When binding time comes, the getBindings() call returns something like:

0 = "NAME%"
1 = "NAME%"
2 = "123456%"
3 = "123%"

For a statement that reads like:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like ? then 15
    when phone like ? then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like ? 
   or upper(alpha) like ?) 
) as temp_table where row_num between 6 and 11

So the final query would be something like:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like 'NAME%' then 15
    when phone like 'NAME%' then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like '123456%' 
    or upper(alpha) like '123%') 
) as temp_table where row_num between 6 and 11

When it should be:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like '123456%' then 15
    when phone like '123%' then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like 'NAME%' 
    or upper(alpha) like 'NAME%') 
) as temp_table where row_num between 6 and 11

In short, moving the orderings to the select to get the pagination working while not moving the bindings to match, results in the bindings attachings to the wrong markers. I worked around the issue by manually binding the order parameters a second time to the select component and moving the ordering portion of the query before the wheres in my method. I'm trying to think of a way to do this in DB2Grammer though, so it would work as expected. Any suggestions?

Thanks for reading.