Level 51
Can you share your migrations and models for both tables
Oct 16, 2018
4
Level 2
Count from two unrelated tables
I have two tables employee attendance and employee encounters.
These two tables do not have a direct relations however they have the same field user_id.
Is it possible to do this. I am trying to build a report like this.
Employee Name | Attendance Counts | Compliance Counts
This is what I have so far for the employee and attendance.
$employees = Employee::where('status', 5)->orderBy('last_name')->paginate(100);
$employeesCount = Attendance::whereBetween('date', ['2018-07-01', '2018-09-30'])
->whereIn('user_id', $employees->pluck('id')->toArray())
->select('user_id', DB::raw('count(*) as total'), DB::raw('count(IF(occurance_type = 0,1,NULL)) hour'), DB::raw('count(IF(occurance_type = 2,1,NULL)) t5'), DB::raw('count(IF(occurance_type = 4,1,NULL)) t120'), DB::raw('count(IF(occurance_type = 6,1,NULL)) b'), DB::raw('count(IF(occurance_type = 7,1,NULL)) ncns'), DB::raw('count(IF(occurance_type = 8,1,NULL)) co'), DB::raw('count(IF(occurance_type = 9,1,NULL)) ntc'), DB::raw('count(IF(occurance_type = 11,1,NULL)) lo'))
->groupBy('user_id')
->get();
$employees->map(function ($employee) use ($employeesCount) {
$count = $employeesCount->where('user_id', $employee->id)->first();
if (!$count) {
$employee->hour32 = 0;
$employee->late = 0 ;
$employee->late120 = 0;
$employee->blackout = 0;
$employee->nocall = 0;
$employee->calloff = 0;
$employee->ntc = 0;
$employee->lo = 0;
} else {
$employee->hour32 = $count->hour * 0;
$employee->late = $count->t5 ;
$employee->late120 = $count->t120 * 3;
$employee->blackout = $count->b * 7;
$employee->nocall = $count->ncns * 7;
$employee->calloff = $count->co * 3;
$employee->ntc = $count->ntc * 2;
$employee->lo = $count->lo * 2;
}
$employee->total = $employee->hour32 + $employee->late + $employee->late120 + $employee->blackout + $employee->nocall +$employee->calloff + $employee->ntc + $employee->lo;
return $employee;
});
Level 2
Attendance Model
<?php
namespace Vanguard;
class Attendance extends Model
{
protected $table= 'attendance';
public $timestamps = true;
public function employee()
{
return $this->belongsTo(Employee::class, 'user_id', 'user_id');
}
public function punch()
{
return $this->belongsTo(schedule::class, 'schedule_id', 'id');
}
public function type()
{
return $this->belongsTo(AttendanceOccurance::class, 'occurance_type');
}
}
Attendance Migration
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class NewAttendanceTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('attendance', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
$table->string('user_id')->index();
$table->string('occurance_type')->index();
$table->date('date');
$table->string('added_by');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('attendance');
}
}
Compliance Controller
<?php
namespace Vanguard;
class EmployeeEncounters extends Model
{
public function Employee()
{
return $this->belongsTo(Employee::class, 'user_id', 'user_id');
}
public function Policies()
{
return $this->hasOne(Policies::class, 'id', 'policy');
}
public function EncounterAttachment()
{
return $this->hasMany(EncounterAttachment::class, 'pid', 'id');
}
public function EncounterNote()
{
return $this->hasMany(EncounterNote::class, 'pid', 'id');
}
public function EncounterReport()
{
return $this->hasMany(IncidentReports::class, 'incident_id', 'id');
}
}
Compliance Migration
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class NewTableEmployeeEncounters extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('employee_encounters', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
$table->date('doi');
$table->string('user_id', '10');
$table->string('encounter_type', '4');
$table->string('department', '4');
$table->string('policy', '4');
$table->string('follow_up', '2');
$table->date('fu_date');
$table->text('incident_report');
$table->text('plan');
$table->string('associated');
$table->string('added_by');
$table->string('status');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('employee_encounters');
}
}
Level 2
Bump any luck
Level 51
@joshblevins - to be honest I'm struggling a bit trying to understand your schema but it sounds like you have a hasManyThrough relationship between Attendance and encounter.
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
I would have a look at https://laracasts.com/series/eloquent-relationships/episodes/4 for background.
Once you can get all your database records you then use groupby to get the counts.
Please or to participate in this conversation.