Excessive database query when getting resource index (select ... from ... where id is null)

[ruark]

• Laravel Version: 11.27.2
• Nova Version: 4.35.3
• PHP Version: 8.2.24
• Database Driver & Version: oci8 (yajra/laravel-oci8 11.6.2)

Description:

When we open the resource index, the system makes a strange query to the database like this:
select * from (select * from "TABLE" where "TABLE"."ID" is null) where rownum = 1

In this case, "id" is the primary key. This behavior is implemented in the method findModel($resourceId = null) in the trait \vendor\laravel\nova\src\Http\Requests\InteractsWithResources.php

Such a query is executed every time when accessing the index of any resource that does not carry any logic. However, given a large table in the database, it greatly affects performance.

[crynobone]

Unable to reproduce the issue, please provide full reproducing repository based on fresh installation as suggested in the bug report template (or you can refer to https://github.com/nova-issues for example)

[ruark]

public function findModel($resourceId = null)
    {
        return rescue(function () use ($resourceId) {
            return $this->findModelOrFail($resourceId);
        }, $this->model(), false);
    }

What happens if $resourceId = null?
In this case, the database query that I wrote about above will be generated.
As far as I understand, in this case an instance of the model should be returned, but this does not happen.

[crynobone]

What happens if $resourceId = null?

In what scenario does it become null in resource index view? We need reproducing repository to replicate the issue.

[ruark]

As far as I understand, this happens when the available actions are received.

vendor/laravel/nova/src/Http/Controllers/ActionController.php

 public function index(NovaRequest $request)
    {
        $resourceId = with($request->input('resources'), function ($resourceIds) {
            return is_array($resourceIds) && count($resourceIds) === 1 ? $resourceIds[0] : null;
        });

        $resource = $request->newResourceWith(
            $request->findModel($resourceId) ?? $request->model()
        );

[ruark]

I saw this when I logged all the queries being executed to the database.

[ruark]

In what scenario does it become null in resource index view?

And in principle, it doesn't matter in what scenario this can happen, if the findModel function can take an argument $resourceId = null

[crynobone]

Unable to replicate this on MySQL or SQLite. Seem to be specific issue to Oracle which we don't have official support.

[ruark]

Unable? ok

I reproduced the same thing in Postgres.

In app/Providers/AppServiceProvider.php add in register method:

DB::listen(function ($query) {
            Log::debug($query->sql, $query->bindings);
        });

Then launch any page with the resource index. After that, look at the log.

Line 16:
[2024-10-24 03:52:32] local.DEBUG: select * from "users" where "users"."id" is null limit 1

Full log:

[2024-10-24 03:52:26] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:26] local.DEBUG: select * from "cache" where "key" in (?) ["nova_valid_license_key"] 
[2024-10-24 03:52:27] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:27] local.DEBUG: select * from "nova_notifications" where "notifiable_id" = ? and "nova_notifications"."deleted_at" is null order by "created_at" desc limit 100 [1] 
[2024-10-24 03:52:27] local.DEBUG: select count(*) as aggregate from "nova_notifications" where "read_at" is null and "notifiable_id" = ? and "nova_notifications"."deleted_at" is null [1] 
[2024-10-24 03:52:28] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:28] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:28] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:28] local.DEBUG: select * from "nova_notifications" where "notifiable_id" = ? and "nova_notifications"."deleted_at" is null order by "created_at" desc limit 100 [1] 
[2024-10-24 03:52:28] local.DEBUG: select count(*) as aggregate from "nova_notifications" where "read_at" is null and "notifiable_id" = ? and "nova_notifications"."deleted_at" is null [1] 
[2024-10-24 03:52:29] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:30] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:31] local.DEBUG: select * from "users" order by "users"."id" desc limit 26 offset 0  
[2024-10-24 03:52:31] local.DEBUG: select count(*) as aggregate from "users"  
[2024-10-24 03:52:32] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:52:32] local.DEBUG: select * from "users" where "users"."id" is null limit 1  
[2024-10-24 03:53:24] local.DEBUG: select * from "users" where "id" = ? limit 1 [1] 
[2024-10-24 03:53:24] local.DEBUG: select * from "nova_notifications" where "notifiable_id" = ? and "nova_notifications"."deleted_at" is null order by "created_at" desc limit 100 [1] 
[2024-10-24 03:53:24] local.DEBUG: select count(*) as aggregate from "nova_notifications" where "read_at" is null and "notifiable_id" = ? and "nova_notifications"."deleted_at" is null [1] 

[ruark]

@crynobone

Repo for replicate; https://github.com/ruark/nova-issue-6564