Place for database query builder in Laravel

Question

I can use scopes to perform basic model-related queries (such as select those users who have a birthday today), scopes are obviously placed in Eloquent Models and return an instance of Eloquent Query Builder. But what if I need to perform a more complex query, say group a massive email log table by email type, join with users and emails and apply some aggregates? Since the resulting fields of the query are mixed (email, email type, user, log fields), I have to use Database Query Builder. I'm hesitating thinking of the best place for the call to the builder.

Is can be a static method in the main model where I join other tables to:

    class ConcreteModel extends Model {

      public static function someQuery(): \Illuminate\Database\Query\Builder {
        return \DB::table('some_table')->join('something');
      }

    }

Or maybe rather to place such stuff in services that need it?

Controllers or services could be a good place for such queries. — linuxartisan
@linuxartisan I'm afraid of cluttering my controllers with any business logic, that's why I supposed that the query should be placed at the consuming service as an alternative to model. — super.t
Why don't use repository pattern? You can easily separate the layers for business logic and data structure. — Abdullah Al Shakib
You can call join on Models too. You don't have to use DB facade for that. — Hilmi Erdem KEREN
@Hilmi but Eloquent maps model properties onto a specific table, i.e. one table per model. Using models with joins makes no sense to me (although I may be wrong), that's why I use the facade that returns a collection of StdClass objects. Otherwise I end up with a collection of models with unrelated properties. Invoking such complex custom queries on models can even break model relations if, for example, I don't include the primary key or foreign keys into the select statement of the complex query. — super.t

super.t super.t · Accepted Answer · 2016-11-15T01:25:49

Well I've come up with the idea of using the Visitor pattern for encapsulating complex queries. Visitor objects encapsulate concrete queries, eloquent models can accept visitors passed by the client and invoke standard methods on them passing themselves to the visitor and handing over collections of StdClasses with results from the visitor back to the client. Seems it can work out for me.

Place for database query builder in Laravel

1 Answers