Group By | Relationship Many To Many Laravel 5

0

I'm developing an application in Laravel 5 where I have the tables employees , companies , exams , employee_exams and their respective fields as migration below:

:: Table Companies

public function up()
{
    Schema::create('companies', function (Blueprint $table) {
        $table->increments('id');
        $table->string('razao_social', 200);
        $table->string('nome_fantasia', 200)->nullable();
        $table->char('cnpj',20)->unique();
        $table->char('cnai',20)->nullable();
        $table->char('tel',20)->nullable();
        $table->string('pessoa_contato', 200);
        $table->string('email', 200)->unique();
        $table->date('venc_programas')->nullable();
        $table->enum('tipo', ['Anual','Consultoria'])->nullable();
        $table->enum('status', ['Ativo', 'Inativo']);

        $table->timestamps();
        $table->softDeletes();
    });
}

Employee Table

public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('company_id')->unsigned();
        $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
        $table->integer('office_id')->unsigned();
        $table->foreign('office_id')->references('id')->on('offices'); 
        $table->string('nome', 200);
        $table->enum('sexo', ['Masculino','Feminino']);
        $table->date('data_nascimento');
        $table->char('identidade',20)->unique()->nullable();
        $table->char('cpf',20)->nullable();
        $table->char('pis',20)->nullable();
        $table->char('ctps',20)->nullable();
        $table->char('tel_celular',20)->nullable();
        $table->char('tel_residencial',20)->nullable();
        $table->char('tel_comercial',20)->nullable();
        $table->date('data_admissao')->nullable();
        $table->date('data_aso')->nullable();
        $table->enum('status', ['Ativo', 'Inativo']);

        $table->timestamps();
        $table->softDeletes();
    });


}

:: Exams Table

public function up()
{
    Schema::create('exams', function (Blueprint $table) {
        $table->increments('id');
        $table->string('exame', 150);
        $table->enum('renovacao', ['Semestral','Anual', 'Bienal'])->nullable();
        $table->enum('status', ['Ativo','Inativo']);
        $table->timestamps();
    });
}

:: Employee_Exams Table

public function up()
{
    Schema::create('employee_exams', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('exam_id')->unsigned();
        $table->foreign('exam_id')->references('id')->on('exams')->onDelete('cascade');
        $table->integer('employee_id')->unsigned();
        $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
        $table->date('last_exam');
        $table->date('next_exam'); 
        $table->string('periodical', 100);         
        $table->timestamps();
        $table->softDeletes();
    });
}

In the pivo employee_exams table, I store the data for the next exams for each employee. Below the relationships between the models:

:: Model Company

public function employees(){
    return $this->hasMany('lbo\Employee');
}

public function address_companies(){
    return $this->hasOne('lbo\Address_Company');
}

Model Employee

public function companies(){
    return $this->belongsTo('lbo\Company');
}

public function exams(){
    return $this->belongsToMany('lbo\Exam','employee_exams', 'employee_id', 'exam_id')
        ->withPivot(['last_exam','next_exam','periodical', 'created_at', 'updated_at', 'deleted_at']);
}

:: Model Exam

 public function employees(){
    return $this->belongsToMany('lbo\Employee','employee_exams', 'exam_id', 'employee_id')
        ->withPivot(['last_exam','next_exam','periodical', 'created_at', 'updated_at', 'deleted_at']);
}

:: Model Employee_Exam

public function exams(){
    return $this->hasManyThrough(
        'lbo\Employee', 'lbo\Exam'
    );
}

What I need now is to list the exams that are to be matched in a grouped fashion, as outlined in View below:

My question is how to perform the search with Eloquent or with 'Query Builder and then display the data for the user.

I did something like this:

DB::table('employee_exams')            
        ->join('exams', 'employee_exams_exam_id', '=', 'exams.id')
        ->join('employees', 'employee_exams_employee_id', '=', 'employees.id')
        ->join('companies', 'companies.employee_id', '=', 'employees.id')
        ->where('companies_employee_id', $employee_id)
        ->groupBy('companies_id')            
        ->get();

Unsuccessful. I need help with the above problem.

    
asked by anonymous 14.06.2017 / 19:33

0 answers