Column not found: 1054 Unknown column 'auditable_id' in 'field list' when using 'forceDelete'

[rebbieboi]

PHP Version

8.2.23

Laravel Version

11.27.2

Package Version

13.6.8

Description

The error shows below during model delete

Column not found: 1054 Unknown column 'auditable_id' in 'field list'

DELETE audits
FROM audits
LEFT JOIN (
  SELECT
    laravel_table.*,
    @laravel_row := IF(@laravel_group = auditable_id,
    @laravel_row + 1,
    1) AS laravel_row,
    @laravel_group := auditable_id
  FROM (
    SELECT
      @laravel_row := 0,
      @laravel_group := 0
  ) AS laravel_vars,
  (
    SELECT id
    FROM audits
    WHERE
      audits.auditable_type = App\Models\ModelName
      AND audits.auditable_id = 1796
      AND audits.auditable_id IS NOT NULL
    ORDER BY audits.auditable_id ASC, created_at DESC
  ) AS laravel_table
  HAVING laravel_row <= 2500
  ORDER BY laravel_row
) AS audit_threshold ON audits.id = audit_threshold.id
WHERE
  audits.auditable_type = App\Models\ModelName
  AND audits.auditable_id = 1796
  AND audits.auditable_id IS NOT NULL
  AND audit_threshold.id IS NULL

NOTE: audits have 375287 records

Steps To Reproduce

perform a delete to a model that uses Auditable

Possible Solutions

No response

[willpower232]

What is the table structure of your audits table?

[rebbieboi]

What is the table structure of your audits table?

note that i can still retrieve the audits for the models

[erikn69]

App\Models\ModelName

what type of model is it? some kind of pivot? what extends?
@laravel_row, @laravel_group don't come from a normal relationship

If it is possible a failing test or a minimal installation repo showing the problem

Also wich mysql version?

apparently you are using a legacy version < 8.0
Illuminate/Database/Query/Grammars/MySqlGrammar.php#L141-L164

[rebbieboi]

App\Models\ModelName

what type of model is it? some kind of pivot? what extends? @laravel_row, @laravel_group don't come from a normal relationship

If it is possible a failing test or a minimal installation repo showing the problem

Also wich mysql version?

apparently you are using a legacy version < 8.0 Illuminate/Database/Query/Grammars/MySqlGrammar.php#L141-L164

Not a pivot and only extends audit, it's still able to retrieve the audit logs for view the problem only happens during delete.

so the problem is with the legacy version of MySQL?

MySQL version: Server version: 5.7.35-38-57 Percona XtraDB Cluster (GPL), Release rel38, Revision 5c9f299, WSREP version 31.53, wsrep_31.53

[staudenmeir]

I came here through a comment on laravel/framework#49695 where I contributed the compileLegacyGroupLimit() method that gets called here. The PR added support for eager loading with limits and this method is necessary for MySQL < 8.0.

I haven't used this package, but it doesn't look like the PR should interfere with deleting auditable models, AFAICS (regardless of the DBMS).

Some ideas for debugging:

• What SQL does the package execute here with MySQL 8.0 or any other DBMS?
• What value do you get when putting dd($model->exists); before this line?

The SQL generated by $model->audits()->[...]->limit($threshold) depends on the value of $model->exists:

• If true, a "normal" query with LIMIT at the end is generated. That's the query for lazy loading, e.g. User::find($id)->posts()->limit($limit)->get().
• If false, a more complex query with window functions (or the workaround with @laravel_row for MySQL < 8.0) is generated. This query is necessary for eager loading.

[rebbieboi]

The problem only occurs when using forceDelete since the model is gone when it enters the prune function and is then redirected to use the referenced code above. Is there any way to bypass that altogether when it's using forcedelete or the model doesn't exist anymore?

[parallels999]

Is there any way to bypass that altogether when it's using forcedelete or the model doesn't exist anymore?

you could create a custom driver that fits your needs
Example: https://laravel-auditing.com/guide/audit-drivers.html#creating-a-custom-driver

[rebbieboi]

I was able to fix this by creating a custom driver to handle forceDelete() using the sample above. Also, I couldn't make the artisan command work so I just created the files manually.