Searching for activity on both a model and a related model

[sburkett]

Great package! Definitely a timesaver that adds a tremendous amount of value. :) This may very well be more of an Eloquent question, but I'm not sure, as this is the first time I've dealt with polymorphic classes. I've been pouring through the documentation and past issues to try and find a solution to this. It seems simple enough, but my brain is befuddled at the moment. :)

So I have these two models set up, both of which log activity:

Sku can have many Listings, and a Listing can have only 1 Sku per.

Both models are set up with the appropriate relationship methods as well, so:

$sku->listings() is a hasMany to Listing and $listing->sku() is the belongsTo to Sku

The tables are related in the usual way:

skus.id is a foreign key here: listings.sku_id

What I want to do is to perform a LIKE search against the sku.name column, but hit both Sku records as well as Listing records for matching Skus.

Example: Let's say a given Sku has 2 Listing records related to it. What I want to do is pick up activity for any changes to the Sku model, as well as any related Listing models.

So, I need to search the equivalent of $sku->name as well as $listing->sku->name.

I was fiddling around with has and whereHas, but sadly, they do not support MorphTo relationships :(

I could do this with raw DB statements and some logic around it, but obviously I would rather keep things expressive. This view also has pagination, so I need to paginate the results.

Something like this (though this won't work, of course):

$activity = Activity::orWhere(function($query) use ($searchTerm) {
     $query->QUERY_THE_LISTING
                ->QUERY_THE_SKU_FOR_THIS_LISTING;
});

I would greatly appreciate any clear set of eyes and thoughts on this. My brain is a wee bit frazzled at the moment :) Thanks in advance!

[sburkett]

As expected, no sooner than I posted this, I realized what I needed to do lol. For posterity:

    $searchTerm = $request->identifier;

    if($searchTerm)
    {
      $sku = Sku::where('name', 'LIKE', '%' . $searchTerm . '%')->first();

      if($sku)
      {
        $query = Activity::forSubject($sku)->orWhere(function($query) use ($sku) {
            $query->where('subject_type', (new Listing())->getMorphClass())
                  ->where('subject_id', $sku->listings()->pluck('id'));
        });
      }
    }
    else
    {
      $query = Activity::with('subject', 'causer');
    }

    $result = $query->orderBy('created_at', 'desc')->paginate(20);

Will close this out now :)

[sburkett]

Ok, I'm reopening this (sorry!), as I realized that my solution only works for first Sku that matches. So if you are searching by the entire SKU name, it works, but a partial won't Not quite sure yet as to how to refactor it to handle multiple Sku records that match on the searchTerm. Any ideas?

[Gummibeer]

Hey,

because I think that I got what you want I skipped after

So, I need to search the equivalent of $sku->name as well as $listing->sku->name.

If I'm wrong please forgive me and I will read all the rest. 😄

I had no idea how often I would suggest this as the solution to go the moment I developed it but again I will suggest the tap() and tapActivity() methods.
https://docs.spatie.be/laravel-activitylog/v3/advanced-usage/logging-model-events#tap-activity-before-logged-from-event

The way I would do this is to append a custom property to the properties column in the tap method, let's name it sku_name (captain obvious).

public function tapActivity(Activity $activity, string $eventName)
{
    $activity->withProperty('sku_name', $this->name); // in SKU model
    $activity->withProperty('sku_name', $this->sku->name); // in Listing model
}

If you are on MySQL you can query the activity model by json child (check that the properties column is of type JSON #525 ).

Activity::where('properties->sku_name', $sku->name)->get();

Just as a suggestion: I would go with the sku.id instead of it's name. This will allow you to search for the current sku even if it's name changed. In turn you can't search via fulltext on this column.

I hope that this helps you!?

[sburkett]

@Gummibeer That looks like it will work nicely! Thanks for the suggestion. Off to give it a whirl :) Leaving this open for now, but will close if I don't have anything else to add to this.

[Gummibeer]

Because you got to a solution without any adjustments I will also refactor your last code snippet (blind shot - untested).

$skus = Sku::where('name', 'LIKE', '%' . $searchTerm . '%')->get();

$query = Activity::forSubject($sku)->orWhere(function($query) use ($skus) {
    $query
        ->where('subject_type', (new Listing())->getMorphClass())
        ->whereIn('subject_id', $skus->pluck('listings')->flatten(1)->pluck('id'));
});

The part where I pluck and flatten and pluck is the part where I'm unsure. Could be that collapse() also works instead of flatten(). If it doesn't work copy'n'paste try to fiddle a bit with this line.
https://laravel.com/docs/5.8/collections#available-methods

[sburkett]

Hmm. Does tapActivity() work with objects of a class that has extended Activity? I have a custom class (ActivityLog) which adds some other functionality to it.

class ActivityLog extends \Spatie\Activitylog\Models\Activity

Getting Call to undefined method App\\ActivityLog::withProperties() ... will keep digging.

[Gummibeer]

Foo - I'm sorry, the withProperty() method was from the service not the model.

$activity->properties = $activity->properties->put('sku_name', $sku->name);

This should do the trick.

[sburkett]

Yep, that did the trick!

Also, messing around with your rewritten code above. The forSubject() scope expects a single Model instance ... not a collection.

[Gummibeer]

In this case duplicate the orWhere for the SKU model and just pluck the sku IDs.

But I'm happy that you got something working.