Laravel filtering hasMany results

Question

I have three tables - Campaigns, Actions and Activists. A Campaign has many Actions, and an Action belongs to both an Activist and a Campaign.

Each action has a client_id (from the client_id of the campaign it belongs to), so when a client views a list of activists, they should only see those who have taken an action on one of their campaigns.

Likewise, when viewing an individual activist, they should only see those actions related to their campaigns.

Models

Campaign.php

public function actions()
{
    return $this->hasMany('Action');
}

Action.php

public function campaign()
{
    return $this->belongsTo('Campaign', 'campaign_id');
}

public function activist()
{
    return $this->belongsTo('Activist', 'activist_id');
}

Activists.php

public function actions()
{
    return $this->hasMany('Action');
}

Controllers

ActivistsController.php

public function index()
{
    $activists = Activist::with('actions')->whereHas('actions', function($q) {
        $user = Sentry::getUser();
        $q->where('client_id', $user->client_id);
    }))->get();

    foreach ($activists as $activist)
    {
         $activist->total = $activist->actions()->count();
    }
}

public function getActivist($id)
{
    $activist = Activist::with('actions')->whereHas('actions', function($q) {
        $user = Sentry::getUser();
        $q->where('client_id', $user->client_id);
    })->find($id);

    $activist->total = $activist->actions()->count();
}

I'm seeing the following:

On the /activists page, I'm correctly seeing only those activists who have taken an action related to my client_id, but also every action they've taken. Likewise, count() returns a full count of all the activists' actions.

On the /activists/{id} page, it correctly returns null if the activist hasn't taken any actions related to my client_id, but where they have, I again see all of their actions and a full count.

AFL. There's something blinding obvious I'm missing, right?

Thanks.

[edit] Updated to add:

Using the client_id filter on both with and whereHas rectifies the 'all actions appearing regardless' issue, but the count issue remains (and I'm not sure this is remotely the right way to improve this):

ActivistController.php

public function index()
{
    $filter = function($q) {
        $user = Sentry::getUser();
        $q->where('client_id', $user->client_id);
    };

    $activists = Activist::with(array('actions' => $filter))
    ->whereHas('actions', $filter)
    ->get();
}

public function getActivist($id)
{
    $filter = function($q) {
            $user = Sentry::getUser();
            $q->where('client_id', $user->client_id);
    };

    $activist = Activist::with(array('actions' => $filter))
    ->whereHas('actions', $filter)
    ->find($id);
}

maybe you can try this https://github.com/fico7489/laravel-eloquent-join — fico7489, Dec 17 '17 at 18:42

score 13 · Accepted Answer · edited May 23 '17 at 12:01

I've solved this now, but for reference:

$activist->actions()->count()

This, obviously in hindsight, ignores any of the prior queries and simply counts data returned from the actions() method as defined in the activist model.

I should have provided an alternate method in the model that includes the appropriate where function, like so:

public function actionsClient($id)
{
    return $this->hasMany('Action')->where('client_id', $id);
}

Meaning the count could then be invoked with:

$activist->total = $activist->actionsClient($id)->count();

for a single campaign and

foreach ($activists as $activist)
{
    $activist->total = $activist->actionsClient($activist->id)->count();
}

on the index. I'd previously tried this, but as described here - How to access model hasMany Relation with where condition? - relations must be described in camelCase (actions_client > actionsClient).

score 0 · Answer 2 · answered Sep 16 '17 at 09:41

0

In my usage this worked for me:

$clients = Profile::select('id', 'name')->orderBy('initial')->whereHas('type', function ($query) {
    $query->where('slug', 'client');
})->office()->pluck('name', 'id');

answered Sep 16 '17 at 09:41

Sinan Eldem

5,564
3
36
37

score 0 · Answer 3 · answered May 28 '18 at 08:33

You already have the instance of $activist eager loading their actions, meaning you already know the actions of the activist beacause they are already in the instance, so why call actions() again instead of just doing this:

$activist->actions->count()

Laravel filtering hasMany results

3 Answers3