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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.