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.