Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have this models in Laravel-5.8:
class Employee extends Model
{
public $timestamps = false;
protected $table = 'employees';
protected $primaryKey = 'id';
protected $fillable = [
'id',
'first_name',
'last_name',
'hr_status',
'employee_type_id',
];
public function employeetype()
{
return $this->belongsTo('App\Models\Hr\EmployeeType','employee_type_id','id');
}
}
class EmployeeType extends Model
{
public $timestamps = false;
protected $table = 'employee_types';
protected $primaryKey = 'id';
protected $fillable = [
'type_name',
'is_active',
];
}
Then I have this Query in Employee controller function:
$published = DB::table('employees AS e')
->leftJoin('goals AS a', function($join) use ($identities)
{
$join->on('a.employee_id', '=', 'e.id')
->where('a.identity_id', '=', $identities)
->whereNull('a.deleted_at');
})
->join('departments AS d', function($join) use ($userCompany)
{
$join->on('e.department_id', '=', 'd.id')
->where('d.company_id', '=', $userCompany);
})
->leftJoin('employees AS em', function($join) use ($userCompany)
{
$join->on('em.employee_code', '=', 'e.line_manager_id')
->where('em.company_id', '=', $userCompany)
->where('em.hr_status', '=', '0')
->where('em.validation_status', '=', 'VALID');
})
->leftJoin('employees AS emm', function($join) use ($userCompany)
{
$join->on('emm.employee_code', '=', 'em.line_manager_id')
->where('emm.company_id', '=', $userCompany)
->where('emm.hr_status', '=', '0')
->where('emm.validation_status', '=', 'VALID');
})
->leftJoin('employees AS eh', function($join) use ($userCompany)
{
$join->on('eh.employee_code', '=', 'd.hr_business_partner_id')
->where('eh.company_id', '=', $userCompany)
->where('eh.hr_status', '=', '0')
->where('eh.validation_status', '=', 'VALID');
})
->where('e.company_id', '=', $userCompany)
->where('e.hr_status', '=', '0')
->where('e.validation_status', '=', 'VALID')
->select(
'e.employee_code',
DB::raw('CONCAT(e.first_name, " ", e.last_name) AS fullname'),
'e.email',
DB::raw('(CASE WHEN a.is_approved = 0 THEN "DRAFT" WHEN a.is_approved = 1 THEN "AWAITING APPROVAL" WHEN a.is_approved = 2 THEN "NOT APPROVED" WHEN a.is_approved = 3 THEN "APPROVED" ELSE "NOT STARTED" END) AS is_approved'),
DB::raw('(CASE WHEN a.line_manager_mid_year_approved = 0 THEN "DRAFT" WHEN a.line_manager_mid_year_approved = 1 THEN "AWAITING APPROVAL" WHEN a.line_manager_mid_year_approved = 2 THEN "NOT APPROVED" WHEN a.line_manager_mid_year_approved = 3 THEN "APPROVED" ELSE "NOT STARTED" END) AS line_manager_mid_year_approved'),
'd.dept_name',
'l.location_name',
'e.grade_level_name',
DB::raw('CONCAT(em.first_name, " ", em.last_name) AS manager'),
'em.email AS manager_email',
DB::raw('CONCAT(emm.first_name, " ", emm.last_name) AS manager_manager'),
DB::raw('CONCAT(eh.first_name, " ", eh.last_name) AS hrbp')
)
->distinct()
->get();
employee_types is another table. How do I include employee_types.is_active = 1;
->where('e.employee_type_id', '=', employee_types.id)
->where('em.employee_type_id', '=', employee_types.id)
->where('eh.employee_type_id', '=', employee_types.id)
->where('emm.employee_type_id', '=', employee_types.id)
in appropriate places (especially the leftjoins for employees) in the query: $published as shown above
I'm not even going to try to make sense of that query.
I suggest you use DB:select() instead and write it in plain SQL.
DB:select('SELECT * FROM table );
Some suggestions
@tray2 - If I use view or store procedure, how do I factor all these parameters in:
$userCompany = Auth::user()->company_id;
$identities = DB::table('appraisal_identity')->select('id')->where('company_id', $userCompany)->where('is_current', 1)->pluck('id');
When writing SQL for a view you generalize the query a bit. Let's say that you have Authors, Books and Genres. To make a view of these three tables you can do this. Very simplified of course.
(I use the old join syntax because I think that is more easy to read.)
SELECT b.title,
a.name,
g.genre
FROM books b,
authors a
genres g
WHERE b.author_id = a.id
AND b.genre_id = g.id
And to create the view
CREATE OR REPLACE VIEW book_views AS
SELECT b.title,
b.year,
a.name,
g.genre
FROM books b,
authors a
genres g
WHERE b.author_id = a.id
AND b.genre_id = g.id
And to query it
BookView::where('name', 'Robert Jordan')->orderBy('year', 'asc')->get();
The benefits from this is that your Eloquent query is less complex, you only get the fields you need and you can sort and filter on any of the fields.
A slightly more complex query,
SELECT
(SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
FROM authors a, author_books ab
WHERE a.id = ab.author_id
AND ab.book_id = b.id) author_id,
(SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
FROM authors a, author_books ab
WHERE ab.author_id = a.id
AND ab.book_id = b.id) author_name,
b.id book_id,
b.title,
(SELECT ROUND(AVG(s.score), 1)
FROM scores s
WHERE s.item_id = b.id
AND media_type_id = 1) rating,
b.series,
b.part,
b.released,
g.id genre_id,
g.genre,
f.id format_id,
f.format,
CASE series
WHEN 'Standalone'
THEN b.released
ELSE (SELECT MIN(bi.released)
FROM books bi
WHERE bi.series = b.series)
END series_started
FROM books b,
genres g,
formats f
WHERE b.genre_id = g.id
AND b.format_id = f.id"
In this query I use a few more tables and use some concatenation to display the author names. A pivot table is also used since a book can have more than one author.
And now to the real benefit of using a view.
$books = BookView::orderBy('author_name')
->orderBy('series_started')
->orderBy('part')
->orderBy('released')
->orderBy('title')
->get();
The eloquent is extremely readable.
So if I want to add filtering it's very easy. I can do this
$books = BookView::where('author_name', 'like', '%' . $search .'%')
->orderBy('author_name')
->orderBy('series_started')
->orderBy('part')
->orderBy('released')
->orderBy('title')
->get();
Or even a stored procedure.
Hi @noblemfd
Your query is too complex, the solution to make it simpler is to use view (DB mysql or postresgql view).
You can create view from tables and even from others views, that should make things more readable.
Have a look how to use db view in Laravel here https://www.itsolutionstuff.com/post/how-to-use-mysql-view-in-laravelexample.html
and see more info in mysql/posgresql doc if needed.
Please or to participate in this conversation.