Laravel Eloquent : Count data from two tables

Question

I have two different tables named artists and artworks. I want to retrieve data from both tables and show them in Summary table.

Condition here is : get name (there are other columns in this table) from artists table and get the number of total artworks from artworks table. Show them in Summary table.

artists |-------------------| | id | name | |-------------------| | 1 | A | |-------------------| | 2 | B | |-------------------| | 3 | C | |-------------------| artworks |-----------------------------------------------------| | id | artist_id | title | medium | |-----------------------------------------------------| | 1 | 1 | ABC | Oil | |-----------------------------------------------------| | 2 | 1 | DEF | Water | |-----------------------------------------------------| | 3 | 1 | GHI | Water | |-----------------------------------------------------| | 1 | 2 | JKL | Oil | |-----------------------------------------------------| | 2 | 2 | MNO | Water | |-----------------------------------------------------| | 3 | 3 | PQR | Oil | |-----------------------------------------------------|

This is what I want:

Summary |-------------------------------------------| | No | Artist Name | Total Artwork | |-------------------------------------------| | 1 | A | 3 | |-------------------------------------------| | 2 | B | 2 | |-------------------------------------------| | 3 | C | 1 | |-------------------------------------------|

Any help will be highly appreciated. Thanks for your time.

Have you added a relationship in the model? or you want to use a DB query? — Dilip Hirapara
– Dilip Hirapara, Commented May 25, 2020 at 13:09

Repox · Accepted Answer · 2020-05-25 12:58:47Z

3

You can use the withCount() method on your relationship:

$artists = Artist::withCount('artworks')->get(); foreach($artists as $artist) { echo $artist->artworks_count; }

answered May 25, 2020 at 12:58

Repox

15.5k9 gold badges58 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tariqul Islam Over a year ago

this requires key relationship between two tables, right?

Dilip Hirapara Over a year ago

Yes, you need to add hasMany relation in Artist model.

Repox Over a year ago

@TariqulIslam Yes. You already have the table schematics enabled for it, so you just have to add the relationship references to your models.

Dilip Hirapara · Accepted Answer · 2020-05-25 13:21:20Z

as you want to use DB query then you need to add join.

\DB::table('artists') ->join('artworks', 'artists.id', '=', 'artworks.artist_id') ->select('artists.id as id', 'artists.name as name', \DB::raw("count(artworks.artist_id) as count")) ->groupBy('artists.id') ->get();

And if you want to use Relation then use hasMany relation in Artist model.

<?php namespace App; use Illuminate\Database\Eloquent\Model; class Artist extends Model { protected $table = 'artists'; public function artworks() { return $this->hasMany('App\Artwork','artist_id','id'); } }

In controller

$artists = Artist::withCount('artworks')->get(); foreach($artists as $artist) { echo $artist->artworks_count; }

Thanks. I followed the second approach as it looks more efficient. It worked.

Collectives™ on Stack Overflow

Laravel Eloquent : Count data from two tables

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related