MySQL Views: LogicException Table does not have a primary key

[bohacf]

Version: 3.2

Bug Description

Nette/Database/Table/Selection can't work with database views because they don't have a primary key and $selection->getPrimary() throws an exception.

Full Description

Here is a detail from this unresolved post: https://forum.nette.org/en/35718-logicexception-table-does-not-have-a-primary-key

Hi, is there any way to disable the PK check please? I use the ublaboo/datagrid and \PdfResponse libraries, and in practice it turns out that it is much more convenient to use view to display data (which contains dozens of joins and conditions) than to write joins in PHP for both practical and functional reasons, reusability and uniform placement. Moreover, it is not possible to write advanced joins in PHP that would support e.g. “outer apply” (in MSSQL). View obviously don't have a primary ID. I also use tabular functions to stretch data to datagrid/PDF, which also don't have PK. I have $grid->setPrimaryKey(‘’) defined in datagrid, but still this error returns from time to time and quite regularly. I have stored the id as a persistent parameter. It's pretty stupid to instruct users to try clicking the button again in case it doesn't work…

Proposed solution

1. It is probably most appropriate to only check for the existence of PKs for tables and not for views, table functions, etc.
2. To be able to specify directly at db model level a column with PK
3. Or that the error be shown as a warning in the log and not as an error to the user.

I understand that IDs are used for the following reasons: speed, paging, to ensure correct identification during update/insert. But in the view primary it is not possible to specify the ID…

Possible workaround

custom $selection->getPrimary()

https://forum.nette.org/cs/36357-database-explorer-pro-databazovy-pohled-view#p226108
https://forum.nette.org/cs/34856-planuje-se-exploreru-pridat-podporu-datovych-typu-jako-to-maji-formulare-a-latte#p217958

[dg]

Why are you using Selection as your data source? Use $connection->query() directly.

[bohacf]

Why are you using Selection as your data source? Use $connection->query() directly.

See the original post: query() cannot be used in ublaboo/datagrid (Contributte Datagrid), and I believe other grids behave similarly. Generally, it would be beneficial to have the ability to access data from a table, view, or function/procedure using a single tool (Selection).

The only issue is the missing primary key for views; otherwise, Selection works perfectly with views as well. You just need to create your own derived classes.

[dg]

I didn't find the reason why you have to use Selection in the original post. Anyway, the Selection layer needs to know the PK by the principle of its work. But you can use StaticConventions and specify the primary key hardcoded. It can be set up probably via conventions: static https://doc.nette.org/en/database/configuration

[bohacf]

I see. But wouldn't it be more appropriate to allow set PK via $table->setPrimary(string|array|null $name, bool $throw = true) and by default throw an exception if the primary key has already been set?

DiscoveredConventions works well, except for those views.