php - Wrong table formatting for sqlserver grammar
I am using the Laravel query builder for fetching some data from a sqlsrv database. The table I am looking for isdbo.[Reukema Blocq Maneschijn BV$Time Slot (Weighbridge)]. Not my choice. The database is developed by an other company.
I keep getting aGeneral error: 20018 Invalid object name error when executing my query. I double checked the name for typos but I couldn't find one.
What I did find was a problem in compiling the query. This is my code to test the problem:
$query = $connection->query()
->from('Reukema Blocq Maneschijn BV$Time Slot (Weighbridge)')
->toSql()
When I dump the$query, I get the following:
string(67) "select * from [Reukema Blocq Maneschijn BV$Time Slot ](Weighbridge)"
The] is in the wrong place here.
This problem keeps existing, even when I'm using theraw() helper:
$query = $connection->query()
->from($connection->raw('dbo.[Reukema Blocq Maneschijn BV$Time Slot (Weighbridge)]'))
->toSql()
Even in this last example, the] moves from the end of the string, to just before the(.
Why is this? Am I missing an important rule/mechanic for compiling sqlsrv queries?
The following code does work. That makes it extra weird for me:
$connection->select('select [Time Slot] as [id] from [Reukema Blocq Maneschijn BV$Time Slot (Weighbridge)]');
So I think there is something wrong in the[email protected] method. Why does Laravel compile it like this?
Answer
Solution:
Personally, I would highly recommend changing the object's name; that is an awful choice. If you can't do this, perhaps create a synonym that is far more "usable". For Example:
Then in your application code call the synonym instead:
Answer
Solution:
After doing some tests with xDebug. I found the issue. It's the
Illuminate\Database\Query\Grammars\[email protected]method.Laravel mistakenly sees my table name as a table valued function. That's why the compilation of the query isn't working correctly.
The fix I used for now is a little bit dirty:
By adding a alias to the end of my
frommethod, the table name get's compiled in an other way. This prevents the table valued function compilation.I'm not sure if I should report this as a bug. Or if the table name I have to work with simply is invalid.
Edit: I reported the bug and created a fix. You can find the issue (and PR) here