Laravel count related data

Question

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.

Jarek Tkaczyk · Accepted Answer · 2014-05-14 18:47:30Z

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

Great share, I had some problem with hasManyThrough relation on my side, but I believe this clears my doubt. Very precise. Thanks.

Jeff Lambert · Accepted Answer · 2014-05-14 18:38:33Z

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.

This is going to query DB in those loops heavily, unless you use eager loading.

Chintan Parekh · Accepted Answer · 2014-05-14 18:44:59Z

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(); } }

Collectives™ on Stack Overflow

Laravel count related data

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related