Handling complex dynamic queries in Laravel 5

by morbidCode   Last Updated September 10, 2018 18:05 PM

I am developing a project using Laravel. My workflow is:

  • route directs to controller
  • Controller calls eloquent models
  • Eloquent models perform queries (tipically CRUD and find)
  • Controller returns either a view or a JSON string

Lately I realise my queries are getting too complex. Complex enough that I find myself writing some parts of the query by hand. For example:

->join('positions', 'employees.position_id', '=', 'positions.position_id')
->where(['employees.project_id'=>$project_id, 'employees.employment_type'=>'agency', 'positions.type'=>'worker', 'employees.status'=>'active'])
->orderBy('employees.first_name')->select('employees.*', 'positions.name as position_name', 'positions.level as position_level'))
->filter(function($query) use($searchValue)
{ // really easier as raw than calling orWhere
    $query->whereRaw("(LOWER(`employees`.`first_name`) LIKE '%$searchValue%' or LOWER(`employees`.`middle_name`) LIKE '%$searchValue%' or LOWER(`employees`.`last_name`) LIKE '%$searchValue%' or LOWER(`positions`.`name`) LIKE '%$searchValue%')");

This query is in a controller. This means my controllers are doing two things now. Generating dynamic queries and redirecting requests and responses. Furthermore, this query might be put in some function for reuse (also in the controller).

I now have two places that does database queries: controllers and models (though eloquent generates it by itself). It looks like the queries are not abstracted enough.

I've thought of two solutions.

  • Put all queries inside Eloquent. This presents a problem if I chose to use a normal query builder instead of an eloquent query builder.
  • Create another object that handles query generation. For example, an EmployeeRepository contains all queries that concerns employees. This fixes the problem on the first solution, I can use any query builder freely, but it seems like I am just creating too much abstraction. And this can be too much work for some very trivial tasks (E.G. eloquent has save method, should I create a save method on the repository just so the controller can call it?).

These solutions do not seem to be ideal. Am I right? If yes, is there a better way?

Related Questions

username exists in two database table

Updated July 18, 2016 08:02 AM

View toArray() laravel 5

Updated August 11, 2016 08:03 AM

Update image using ajax in laravel

Updated February 26, 2017 02:05 AM

What is the laravel way to include our own PHP classes?

Updated November 27, 2016 08:02 AM

Using Laravel with DAO instead of Eloquent ORM

Updated June 23, 2017 13:05 PM