Integrity constraint violation when model pivot is saved

[cristianlabadie]

Q	A
Bug?	yes
New Feature?	no
Framework	Laravel	Lumen
Framework version	10.x.y
Package version	13.x.y
PHP version	8.0.2

Actual Behaviour

I have a model that is extending from Illuminate\Database\Eloquent\Relations\Pivot and implements AuditableContract as indicated in the documentation. This model has $casts given that when it is instantiated, it shows the formatted values.

So in the controller, I instantiate the pivot model to be able to register the pivot. However, when I try to save it, I get this error:

Integrity constraint violation: 1048 Column 'auditable_id' cannot be null.

Expected Behaviour

I hope to be able to perform the registration and that it can be properly recorded with all the necessary information for analysis in case of any error.

Steps to Reproduce

1. Create a model and define a many-to-many relationship. For example, create a model called Empresa and define a many-to-many relationship called productos.
2. Create a pivot model called EmpresaProductoPivot and make it extend the Pivot class. Apply the audit and contracts traits to this model.
3. In a controller, instantiate the EmpresaProductoPivot model and create an intermediate record between empresa and producto.

Possible Solutions

If you have any ideas on how to solve the issue, add them here, otherwise you can omit this part.

[parallels999]

https://github.com/owen-it/laravel-auditing/issues/628#issuecomment-1056534971

[cristianlabadie]

I tried to use that approach, but when the audit is registered, it is not associated with the corresponding model, and the "new values" it saves is the complete model. It's not clear which values with existing data were actually recorded.

[parallels999]

are you using auditAttach, auditDetach and auditSync??

[cristianlabadie]

No, I am using the method of registering models as indicated in the Laravel documentation, using Model::create([]); to properly register the model in the auditable_type column and ensure that the auditable_id corresponds to the pivot. Additionally, the state (update, delete, or created) is also stored.

[cristianlabadie]

To maintain the property casting, you need to define the model that extends from Pivot. However, when you try to create a record, it generates an error.

[parallels999]

are you using auditAttach, auditDetach and auditSync??

No

Sorry, seems like you are trying a custom way, i can't help on that case without a failing demo

I can only tell you that AuditableContract is not designed to be added on Eloquent\Relations\Pivot

Try adding protected $primaryKey = 'YOUR_PIVOT_PK';

https://github.com/owen-it/laravel-auditing/blob/8b9c5bd7d6e79ed63376a86b1861ab1606215801/src/Auditable.php#L641-L750 https://github.com/owen-it/laravel-auditing/blob/8b9c5bd7d6e79ed63376a86b1861ab1606215801/tests/Functional/AuditingTest.php#L589-L628

[cristianlabadie]

this way is how i prevent this error

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'auditable_id' cannot be null (Connection: auditDB, SQL: insert intoaudits(old_values,new_values,event,auditable_id,auditable_type,user_id,user_type,tags,ip_address,user_agent,url,updated_at,created_at) values ([], {"producto_id":60,"empresa_id":971}, created, ?, App\Models\ProductoEmpresaPivot, 8008, App\Models\User, ?, 127.0.0.1, Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36, http://127.0.0.1:8001/empresa/guardarProducto/971, 2023-07-06 10:06:19, 2023-07-06 10:06:19))

if i change the extend pivot to model it create record like this

INSERT INTO `audits` (`id`, `user_type`, `user_id`, `event`, `auditable_type`, `auditable_id`, `old_values`, `new_values`, `url`, `ip_address`, `user_agent`, `tags`, `created_at`, `updated_at`)
VALUES
    (11769340, 'App\\Models\\User', 8008, 'created', 'App\\Models\\ProductoEmpresaPivot', 1986, '[]', '{\"id\": 1986, \"empresa_id\": 971, \"producto_id\": 50}', 'http://127.0.0.1:8001/empresa/guardarProducto/971', '127.0.0.1', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36', NULL, '2023-07-06 10:12:14', '2023-07-06 10:12:14');

Is there a way for it to adapt independently of whether the model extends Model or Pivot, considering that the Pivot class also extends Model?

[angeljqv]

https://laravel-auditing.com/guide/audit-custom.html#example-related-attributes

[cristianlabadie]

$empresa->auditAttach('productos', $req->producto['id']);

when i do this im getting this

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'auditable_id' cannot be null (Connection: auditDB, SQL: insert intoaudits(old_values,new_values,event,auditable_id,auditable_type,user_id,user_type,tags,ip_address,user_agent,url,updated_at,created_at) values ([], {\"producto_id\":171,\"empresa_id\":971}, created, ?, App\\Models\\ProductoEmpresaPivot, 8008, App\\Models\\User, ?, 127.0.0.1, Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36, http://127.0.0.1:8001/empresa/guardarProducto/971, 2023-07-06 10:36:31, 2023-07-06 10:36:31))

in the model Empresa, i define the relationship like this

public function productos()
{
    return $this->belongsToMany('App\Models\Producto', 'producto_empresa')
    ->using(ProductoEmpresaPivot::class)
    ->withTimestamps()
    ->withPivot('cantidad_minima', 'unidades_caja', 'costo_neto', 'proveedor_activo');
}

it requires the using function to apply casting property over the column like proveedor_activo which is Boolean.

[angeljqv]

Please learn to use markdown

 ```php 
 your php code
 ```,

 ```sql
 sql querys
 ```,

 ```(WITHOUT_ANY_IDENTIFICADOR)
 any exception, text, others
 ```,

[angeljqv]

For auditAttach you must remove AuditContract and Auditable, that is your main problem

[cristianlabadie]

Thank you for the advice, but I'm not completely convinced by how the pivot information is stored. The auditable_type depends on the model used for the attach, sync, or detach operations, and in the values JSON, it contains a lot of information. It's not optimal to store the entire object in a sync operation or when updating just a single field.

[crichardson9]

I'm not sure if this will help anyone, but you should ensure that your pivot model is set to incrementing...

/**
 * Indicates if the IDs are auto-incrementing.
 *
 * @var bool
 */
public $incrementing = true;

/**
 * The primary key associated with the table (required on Pivot table for auditing).
 *
 * @var string
 */
protected $primaryKey = 'id';