Laravel 5.6 pivot table auditing using laravel auditing 8.0

[PrafullaKumarSahu]

Q	A
Bug?	no
New Feature?	yes
Framework	Laravel
Framework version	5.6.29
Package version	8.0
PHP version	7.2.10

Steps to Reproduce

As there is no event fired for attach, detach and sync, Laravel auditing does not store records for these operations, but for my requirement, I need to store this, so I tried to follow some solutions provided in git issue discussion, but could not achieve it.

The most important thread was this pull request. After going through this I tried to implement this gist solution. but I am unable to log it in audits_pivot table.

Any help on this will be appreciated.

[vpillinger]

Just fire the appropriate events manually and pass the Pivot object to the Auditor.

You can create audit records following this documentation: http://laravel-auditing.com/docs/9.0/auditor

The key line being Auditor::execute($article)

[PrafullaKumarSahu]

@vpillinger thank you for your kind reply, I tried implementing that, but still could not make it work, let me share the changes I have made.

In config/audit.php

 'events' => [
        'creating',
        'created',
        'updating',
        'updated',
        'saving',
        'saved',
        'deleting',
        'deleted',
        'restoring',
        'restored',
        'pivotAttached',
        'pivotDetached',
        'pivotUpdated'
    ],

and in pivot auditable trait
PivotAuditable.php

<?php
namespace App\Traits;

use Fico7489\Laravel\Pivot\Traits\PivotEventTrait;
use OwenIt\Auditing\Auditable;

trait PivotAuditable
{
    use Auditable, PivotEventTrait;

    public static function bootPivotAuditable()
    {
        static::pivotAttaching(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {});

        static::pivotAttached(function ($model, $relationName, $pivotIds, $pivotIdsAttributes){
            if ($pivotIds) {
                return $model->savePivotAudit(
                    'pivotAttached',
                    get_class($model->$relationName()->getRelated()),
                    $pivotIds[0],
                    $model->getKey()
                );
            }
        });

        static::pivotDetaching(function ($model, $relationName, $pivotIds) {});

        static::pivotDetached(function ($model, $relationName, $pivotIds) {
            if ($pivotIds) {
                return $model->savePivotAudit(
                    'pivotDetached',
                    get_class($model->$relationName()->getRelated()),
                    $pivotIds[0],
                    $model->getKey()
                );
            }
        });

        static::pivotUpdating(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {});

        static::pivotUpdated(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {
            echo 'pivotUpdated';
            echo get_class($model);
            echo $relationName;
            print_r($pivotIds);
            print_r($pivotIdsAttributes);
        });

        function savePivotAudit($eventName, $relationClass, $relationId, $modelId)
        {
            return app('db')->table('audits_pivot')->insert([
                    'event'          => $eventName,
                    'auditable_id'   => $modelId,
                    'auditable_type' => $this->getMorphClass(),
                    'relation_type'  => $relationClass,
                    'relation_id'    => $relationId,
                    'created_at'     => now(),
                    'updated_at'     => now(),
                ]);
        }

        /**
         * normal : $model->audits
         */
        function getPivotAudits($type, $id)
        {
            return app('db')->table('audits_pivot')
                    ->where('auditable_id', $id)
                    ->where('auditable_type', $type)
                    ->get()
                    ->reverse();
        }
        /**
         * with relation : $model->auditsWithRelation
         */
        function getAuditsWithRelationAttribute()
        {
            return $this->audits->map(function ($item) {
                $item['relations'] = $this->getPivotAudits($item->auditable_type, $item->auditable_id);
                return $item;
            });
        }
        
    }
}
?>

In composer.php

 "files": [
            "app/Helpers/Helper.php",
            "app/Traits/PivotAuditable.php"
        ],

and in my controller

public function pick(Request $request, Auditor $auditor)
   {
        $sync = $video->statuses()->wherePivot('status_id', 2)->sync($status);
        if ($audit = $auditor->execute($video)) {
               $auditor->prune($video);
        }
    }

[PizzaTibe]

Hy @vpillinger I am also needing this function. can you explan how you use, please?

[PizzaTibe]

I tried to make work, but give up. but I find a beter pakage https://altek.gitlab.io/accountant
it works out of box for pivot

[vpillinger]

@PrafullaKumarSahu

In config/audit.php

 'events' => [
        'creating',
        'created',
        'updating',
        'updated',
        'saving',
        'saved',
        'deleting',
        'deleted',
        'restoring',
        'restored',
        'pivotAttached',
        'pivotDetached',
        'pivotUpdated'
    ],

This is designed for restricting audit events, not adding new ones. You don't need new events here.

PivotAuditable.php

<?php
namespace App\Traits;

use Fico7489\Laravel\Pivot\Traits\PivotEventTrait;
use OwenIt\Auditing\Auditable;

trait PivotAuditable
{
    use Auditable, PivotEventTrait;

    public static function bootPivotAuditable()
    {
        static::pivotAttaching(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {});

        static::pivotAttached(function ($model, $relationName, $pivotIds, $pivotIdsAttributes){
            if ($pivotIds) {
                return $model->savePivotAudit(
                    'pivotAttached',
                    get_class($model->$relationName()->getRelated()),
                    $pivotIds[0],
                    $model->getKey()
                );
            }
        });

        static::pivotDetaching(function ($model, $relationName, $pivotIds) {});

        static::pivotDetached(function ($model, $relationName, $pivotIds) {
            if ($pivotIds) {
                return $model->savePivotAudit(
                    'pivotDetached',
                    get_class($model->$relationName()->getRelated()),
                    $pivotIds[0],
                    $model->getKey()
                );
            }
        });

        static::pivotUpdating(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {});

        static::pivotUpdated(function ($model, $relationName, $pivotIds, $pivotIdsAttributes) {
            echo 'pivotUpdated';
            echo get_class($model);
            echo $relationName;
            print_r($pivotIds);
            print_r($pivotIdsAttributes);
        });

        function savePivotAudit($eventName, $relationClass, $relationId, $modelId)
        {
            return app('db')->table('audits_pivot')->insert([
                    'event'          => $eventName,
                    'auditable_id'   => $modelId,
                    'auditable_type' => $this->getMorphClass(),
                    'relation_type'  => $relationClass,
                    'relation_id'    => $relationId,
                    'created_at'     => now(),
                    'updated_at'     => now(),
                ]);
        }

        /**
         * normal : $model->audits
         */
        function getPivotAudits($type, $id)
        {
            return app('db')->table('audits_pivot')
                    ->where('auditable_id', $id)
                    ->where('auditable_type', $type)
                    ->get()
                    ->reverse();
        }
        /**
         * with relation : $model->auditsWithRelation
         */
        function getAuditsWithRelationAttribute()
        {
            return $this->audits->map(function ($item) {
                $item['relations'] = $this->getPivotAudits($item->auditable_type, $item->auditable_id);
                return $item;
            });
        }
        
    }
}
?>

I have not really looked at this class too closely, but you don't need this. Instead, you just want to add Auditable to the Pivot Class and use the existing functionality:

class StatusPivot extends Pivot {
use Auditable;
}

Now, syncing doesn't fire an updated event in older versions of Laravel. Therefore, you need to fire the updated event manually or create the audit yourself. What you did is fire the event on $video, but you want to fire the event on the pivot.

 public function pick(Request $request, Auditor $auditor)
    {
         $status_pivot = StatusPivot::where('status_id', $status->id)->where('video_id', $video->id);
         $status_pivot->some_value = $some_other_value;
         if ($audit = $auditor->execute($status_pivot)) {
                $auditor->prune($status_pivot); // execute does this automatically anyway
         }
     }

Now your original code has a couple issues which I think that your running into trouble with this in the first place.

1. I cannot think of a reason why videos to statuses should be a many-to-many relationship. How can a video have multiple statuses at once? What you actually want is something like this:
   Some object (eg. user) >> has many << has one video_statuses >> has one << has many Videos
   This makes the Status object the pivot itself.

2. This line of code shows that you aren't really understanding how to properly use a relationship. here you are syncing a status to a status object, which I am surprised does not throw an exception.

$video->statuses()->wherePivot('status_id', 2)->sync($status);

As far as I can tell, you were trying to do something like:

StatusPivot::where('video_id', $video->id)->where('status_id', 2)->update(['video_id' => $video->id, 'status_id' => 2, 'some_other_value' => $some_other_value])

[PrafullaKumarSahu]

@vpillinger, thank you so much for your kind reply.

Re: 1. video has multiple statuses because parallelly we are performing multiple operations, so based on different operation video has multiple statuses at the same time.

Re: 2. if I want to update a pivot record with this video id and this status, how I should update that as
here code like $user->roles()->sync([1, 2, 3]); does not specify roles, and the selected user may have many records and I would not like to update all.

[vpillinger]

video has multiple statuses because parallelly we are performing multiple operations, so based on different operation video has multiple statuses at the same time.
I can't really provide any information since I do not know what you are trying to accomplish. But it sounds like "status" may actually be a separate object called Operation in your case and you are misusing a pivot table.

A good use for a custom column on a pivot table is something like:

video_id
user_id
access_permission

In this case, an access_permission is some additional value that is inherent to the relationship. In your case, it sounds like you are ending up with this:

video_id
user_id
status
operation or operation_id

At this point, you are not really working with a pivot anymore, but a completely new concept like an Edit or Transformation or something. At this point, you don't even need an audit record on that object, since that object IS the audit record. Additionally, you should consider if the status should just be part of an Operation object.

So it overall sounds like your doing something wrong with your architecture that is causing the pivot issue in the first-place.

Regarding your question on $user->roles()->sync([1, 2, 3]);. I do not follow, here you specify the user to have roles 1, 2, and 3. You can use syncWithoutDetaching if you don't want to delete other roles.

[PrafullaKumarSahu]

@vpillinger

it sounds like you are ending up with this:

video_id
user_id
status
operation or operation_id

no, I am ending up with [video_id, status_id] with 3 records, there is no operation id like thing, for me if 3 operations are done on video, there are 3 statuses that video is related to and for any status, there can be multiple videos.

[parallels999]

closed due to inactivity