Laravel-auditing: Retrieving audits conditionally

[PrafullaKumarSahu]

| Q | A
| ----------------- | ---
| Bug? | no
| New Feature? | no
| Framework | Laravel
| Framework version | 5.6.38
| Package version | 8.0
| PHP version | 7.2.10

I want to retrieve the date something like this.

$video->audits->where([['old_values', '=', '{"video_status_id":3}'], ['new_values', '=', '{"video_status_id":5}']])->pluck('created_at')->first();

I would like to know, if it is possible or if there is any better way to achieve it?

By the way, I am getting error "undefined property audits",

[quetzyg]

Your best bet would be to update the old_values and new_values column types to JSON instead of being TEXT, and you should be able to query them using the JSON where clauses.

[PrafullaKumarSahu]

Hello @quetzyg , I tried something like

$video->taggedDate = DB::table('audits')
                ->whereJsonContains('old_values->video_status_id', [3])
                ->whereJsonContains('new_values->video_status_id', [5])
                ->pluck('created_at')->first();

Getting null at created_at, there is record, but I am not getting those results, Am I doing something wrong, please let me know, also Do I need join to specify this $video on this?

[quetzyg]

You're probably filtering wrong, then.
See if you get anything first, before trying to pluck() stuff.

Replace ->pluck('created_at')->first(); with just ->get(), and if you still get no results, it's one of the whereJsonContains() conditions that's wrong.

Debugging 101, dude.

[PrafullaKumarSahu]

@quetzyg thank you for super quick response, I am getting null for get().

[PrafullaKumarSahu]

@quetzyg I am getting null for get(), but there is record in audits table, so I want to know why this is happening?

[quetzyg]

Because your query is wrong?

[PrafullaKumarSahu]

@quetzyg Can you please let me know, what would be the correct query or what I am doing wrong?

[quetzyg]

Did you at least read the QueryBuilder documentation link concerning JSON queries I left in a previous response?

What about the VERY FIRST example in the docs, but with the attributes you want to filter by, like ->where('old_values->video_status_id', 3) ?

It's a bit infuriating when you don't show much effort to your own problem, and expect others to just solve it for you.

This isn't StackOverflow, and even there people are expected to complement their questions with all the information they can and what they have done so far.

[PrafullaKumarSahu]

What made you think I have not tried , I don't know, but I did tried //
$video->taggedDate = DB::table('audits') ->whereJsonContains('old_values->video_status_id', [3]) ->whereJsonContains('new_values->video_status_id', [5]) ->pluck('created_at')->first();

and

$video->taggedDate = $video->audits->where([['old_values', '=', '{"video_status_id":3}'], ['new_values', '=', '{"video_status_id":5}']])->first(); like stuffs before asking here. By the way I understand it is not stackoverflow, I am among top contributors with my answers in top 5% for quite a few technologies in stackoverflow, so believe me I asked here after putting a good enough effort with what ever understaanding I have on laravel collection filters.

[quetzyg]

What made you think I have not tried

Most of your answers.

But let me do your work for you.

        $video->taggedDate = DB::table('audits')
            ->where('old_values->video_status_id', 3)
            ->where('new_values->video_status_id', 5)
            ->pluck('created_at')
            ->first();

You're welcome.

[PrafullaKumarSahu]

Already done yesterday, I just replied to make you believe, I am not among the people who expect other people to do work for them and just copy pastes. Any way thank you so much.

By the way people will try to retrieve data based on some record's id, that is auditable_id in audits table, so you can add condition to filter that too.

so what I am using is

 $video->taggedDate = DB::table('audits')
                ->where('auditable_id', '=', $video->video_id)
                ->where('old_values->video_status_id', 3)
                ->where('new_values->video_status_id', 4)
                ->pluck('created_at')->first();