CODEWITHSUNDEEP
phplaraveleloquent
Milos Sretin
Milos Sretin

Reputation: 1748

Laravel count related data

I have in my database:

Campaigns
    hasMany Tasks
        hasMany Links
            hasMany LinkClicks

If I query 

Campaign::find(1)->task->count();

It returns the correct number of tasks asigned to that campaign. But how can I count link clicks from Campaign id?

Campaign Model:

public function task(){
    return $this->hasMany('Task','campaign_id');
}

Task Model:

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

Link Model:

public function task(){
    return $this->belongsTo('Task','task_id');
}
    public function clicks(){
            return $this->hasMany('LinkClick', 'link_id');
    }
public function user(){
    return $this->belongsTo('User','user_id');
}

LinkClick Model:

public function link(){
    return $this->belongsTo('Link','link_id');
}

So, I want to count linkClicks only for links under specific Campaign, no matter which Task or Link, ofcourse that task and link needs to be under specified Campaign.

Upvotes: 1

Views: 835

Answers (3)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

Use hasManyThrough for the links:

// Campaign model
public function links()
{
   return $this->hasManyThrough('Link', 'Task');
}

// then you can fetch the count either querying db:
$campaign->links()->count(); // SELECT COUNT(*) ...
// returns STRING '25'

// or from the collection of already loaded relation:
$campaign->links->count(); // counts collection items
// returns INT 25

This is not going to work further, for the LinkClicks, but there's easy way yuou can achieve that too:

// This is another way for the above:
$campaign = Campaign::with('tasks.links')->find($someId);
$campaign->tasks->fetch('links')->collapse()->count(); // INT 25

// and the LinkClicks:
$campaign = Campaign::with('links.clicks')->find($someId);
$campaign->links->fetch('clicks')->collapse()->count(); // INT 555

Upvotes: 4

Chintan Parekh
Chintan Parekh

Reputation: 1101

Since you are using relationships; you can do something like this:

$count = 0;
$campaign_with_tasks = Campaign::with('tasks')->where('id','=',$campaign_id)->get();

foreach($campaign_with_tasks->tasks as $task){
$links =  Links::where('task_id','=',$task->id)->get();
foreach($links as $link){
$count += LinkClicks::where('link_id','=',$link->id)->count();
}
}

Upvotes: 0

Jeff Lambert
Jeff Lambert

Reputation: 24661

Since going down they are all 'hasMany' relations, you will need to iterate over all of the children at each level in order to generate the sum. Something like this ought to work for you:

$campaign = Campaign::find($campaignId);

$count = 0;
foreach($campaign->task as $task) {
    foreach($task->link as $link) {
        $count += $link->click->count();
    }
}

echo 'Clicks for campaign id ' . $campaignId . ': ' . $count . "\n";

Or, you can skip all that and just issue a bare statement against the database:

$results = DB::select(
    'SELECT \'a\' FROM campaign c
        INNER JOIN task t ON t.campaign_id = c.id
        INNER JOIN links l ON t.task_id = t.id
        INNER JOIN link_clicks lc ON lc.link_id = l.id'
);

First idea is probably a better idea though.

Upvotes: 2

Related Questions