[Bug] Incorrect syntax near '.' when loading lists on SQL server

[WrdVdb]

Bug report

What I did

Updated backpack/crud from 5.1.2 to 5.4.7

What I expected to happen

What happened

Every crud list gives an error (MS SQL server and every model has a db schema):

SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '.'. (SQL:

select count(*) as total_rows from (select [comm].[Contact].[ID] from [comm].[Contact]) as [comm].[Contact_aggregator]

)

What I've already tried to fix it

I guess this is a MS sql server only error, but I hope this can be fixed, or changed, so we can keep updating backpack.

It is the dot that causes the problem.

When I change line 250 in backpack/crud/src/app/Library/CrudPanel/Traits/Query.php

from

$outerQuery = $outerQuery->fromSub($subQuery->select($modelTable.'.'.$this->model->getKeyName()), $modelTable.'_aggregator');

to

$outerQuery = $outerQuery->fromSub($subQuery->select($modelTable.'.'.$this->model->getKeyName()), str_replace('.','_',$modelTable).'_aggregator');

the problem is solved.

Is it a bug in the latest version of Backpack?

Yes

Backpack, Laravel, PHP, DB version

When I run php artisan backpack:version the output is:

PHP VERSION:

PHP 7.4.29 (cli) (built: Apr 21 2022 10:16:36) ( NTS )

LARAVEL VERSION:

v8.83.17@2cf142cd5100b02da248acad3988bdaba5635e16

BACKPACK VERSION:

5.4.7@6e1bb116de9f3091530cb9b11edcceb4d252daa8

[pxpm]

Up's, I guess that's on me! Sorry @WrdVdb 😞

I am just wondering why replacing . for _ in the $modelTable (aka $model->getTable()) would solve this. Why do you have a dot on the model table ? What's the output of your $model->getTable() ?

Cheers

[WrdVdb]

I use a ms sql server, and it uses schema's, so schema.table. Without the schema in the model, it does not work.

[pxpm]

@WrdVdb I am sorry, I am not a mssql user myself and don't have good insights on how it works.

So from your comments I guess that in your model you have something like: protected $table = 'schema.table_name' ?

I guess that under the hood eloquent deals with the . for you, but since we are doing raw queries it doesn't ?

Could you provide me a dump of some $query->toSql() (not the query we are trying to fix here, some other query)? Does the dot get replaced by underscore ? So it is something like select * from schema_table instead of select * from schema.table ?

My concern here is with mysql users that use a dot in the table name (it's not advised, but it's possible).

Cheers

[WrdVdb]

So from your comments I guess that in your model you have something like: protected $table = 'schema.table_name' ? That is correct, FI

protected $table = 'comm.Contact';

A query would look like this:

select * from [comm].[Contact] where [Email] = ?

The problem is that a alias is added manually in the query that uses the database name, and this can contain an dot.

[pxpm]

Ok, so the problem is only on the alias name. Pfiewww 😅 , that's easier to solve, and we really don't care about the agreggator name.

I've submited the PR https://github.com/Laravel-Backpack/CRUD/pull/4784

Thanks for raising the issue and sticking with me to better understand it.

I will close this now 🙏

Cheers