CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-5.7
Tariqul Islam
Tariqul Islam

Reputation: 347

Laravel Eloquent : Count data from two tables

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.

Upvotes: 0

Views: 1348

Answers (2)

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

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

Upvotes: 2

Repox
Repox

Reputation: 15476

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

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

Upvotes: 3

Related Questions