Error in laravel query builder

Question

My Laravel query builder return null:

    $country = DB::table('participate_company')

        ->join('company', 'company.company_id', '=', 'participate_company.company_id')
        ->join('country', 'country.country_id', '=', 'company', 'company.country_id')
        ->join('competition', 'competition.competition_id', '=', 'participate_company.competition_id')

        ->select('country.country_name', DB::raw('COUNT(company.country_id) as total'))
        ->groupBy('company.country_id')
        ->groupBy('country.country_name')
        ->get();

Table design:

1. Participate_company

competition_id (pk/fk)
company_id (pk/fk)

2. company

company_id (pk)
company_name
country_id (fk)

3. country

country_id (pk)
country_name

4. competition

competition_id (pk)
competition_year

I want to produce result of count distinct country based on competition year. For example competition year = 2012, country_name = England, count(total) = 20. But my current query produce null.

SQLFiddle : http://sqlfiddle.com/#!9/a2092f/1

Please put a selection of your data on a fiddle and I'd be happy to take a look at it. Thank you... — hd1
– hd1, Commented Jul 9, 2018 at 10:12
@hd1 I had edit my post and add sql fiddle ... please take a look on it thank you — Firdaus
– Firdaus, Commented Jul 10, 2018 at 3:08
what results are you expecting from that query? I was able to get "England" — hd1
– hd1, Commented Jul 10, 2018 at 4:58

Cong LB · Accepted Answer · 2018-08-03 07:45:39Z

1

I suggest using Laravel ORM Relationship and Eager Loading to solve this problem. In Company model, we would define country()method:

public function country() {
       return $this->belongsTo(Country::class, 'country_id', 'id');
}

In Competition model, define method

public function company() {
       return $this->belongsToMany(Company::class);
}

So in controller you can call groupBy :

Competition::with('company:id,country_id')->get()->groupBy('year');

We will catch country_id in each company which is in relations of years. I just tested a simple example, after that, we will loop over this collection and count them .

Hope this's usefull.

P/s. As using by models, my table's names: companies, countries, competitions, company_competition

edited Aug 3, 2018 at 7:45

answered Jul 10, 2018 at 7:28

Cong LB

1085 bronze badges

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

Collectives™ on Stack Overflow

Error in laravel query builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related