CODEWITHSUNDEEP
phpmysqllaravel-5laravel-query-builder
Firdaus
Firdaus

Reputation: 143

Error in laravel query builder

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

Upvotes: 0

Views: 70

Answers (1)

Cong LB
Cong LB

Reputation: 108

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 .

Simple example

Hope this's usefull.

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

Upvotes: 1

Related Questions