[Documentation] Support for db schema, defined in `$table` on Model

[tabacitu]

Reported in https://github.com/Laravel-Backpack/CRUD/issues/5325 by @francescverdu

---

What's happening? In PostgreSQL or SQL Server, it makes sense to have multiple databases, each with multiple schemas, each with multiple tables. Then the only way to define that in Eloquent is to do:

class Product extends Model {

    $connection = 'my_connection';
    $table = 'my_schema.my_table';
}

When that happens @francescverdu has reported that this breaks the CRUD table:

if you specify schema in the $table attribute, even MANUAL COLUMNS are not orderable nor searchable, and this is the true problem.

I've tried to reproduce the issue, but I don't have PostgreSQL installed so I'm working blind here, might be a better idea to pass it to @pxpm to confirm and debug.

Not sure this is a bug... or a feature... 🤷‍♂️ What do you think @pxpm ?

[francescverdu]

Sorry I did'nt specify, I'm working with mysql, the problem can be reproduced with mysql.

[tabacitu]

Huh... why? I mean... afaik MySQL schemas are bullshit, a schema is actually a separate db. So I always thought it only makes sense to use schemas in PostgreSQL and SQL Server... right? Am I missing a benefit of schemas in MySQL @francescverdu ?

[francescverdu]

Well, imagine a multitenant architecture, each user having his tables on a separate schema that laravel redirects upon login...

Or imagine you develop an add-on for an existing third-party database, and you put your additional tables on a separate schema for not interfering with the original application...

[pxpm]

In that case shouldn't you specify that in the config/database.php file ?

I would setup a new connection for the 3rd party db where I set the table prefix or schema along with the connection details ?

AFAIK the current way tenancy packages do this, is by swapping the db config on run-time after the tenant is identified. Have a look for example at: https://github.com/archtechx/tenancy/blob/3.x/src/Bootstrappers/DatabaseTenancyBootstrapper.php

Cheers

[francescverdu]

yes, but there are still some tables that are COMMON to all tenants, so in these tables you must point to the common schema, beacuse these tables will not be found on the current tenant main schema.

now I'll test the same scenario for SqlServer, I'll inform you if the problem also appears.

[francescverdu]

Checked on SqlServer, all seems be working fine, can sort, can search, specifiyng schema desn't change behaviour on very first tests

[ale1981]

This is definitely an issue on SQL Server, I am using SQL Server 2022 and was scratching my head as to why searching etc wasn't working. Luckily I found this thread.

I had the tables in my models set like this;

protected $table = 'DB.dbo.table';

I have now added a new connection in the database config with the database configured and searching etc works as expected.

[pxpm]

From my research there is nothing we Backpack can do here. Maybe document it a little better, because information about this topic is a bit all over the place. But my understanding, the solution is to create multiple connections, or swap the configs on the fly when talking about tenant identification.

I will try to come up with somewhat a FAQ for this on our docs.

[ale1981]

To add to this, even with a separate connection, SQL Server 2022 requires the db user to have the db_owner role assigned for the database you're attempting to access.

I attempted to use views on the local database, so creating a view;

SELECT * FROM db.dbo.table

However, this doesn't work at all, no searching, sorting etc.

[ale1981]

I was able to get this working by returning true from the hasDatabaseColumn() method in the ColumnsProtectedMethods trait.

Is there a way to bypass this check without having to modify the core files?

[pxpm]

I think defining tableColumn => true in your column definition will allow the search logic to work even if we didn't identified it from our custom method and without the need for any overwrite.

I will be closing this, if required please re-open 👍