Where to put not Eloquent related SQL queries

Question

I use Eloquent to implement my models. It has a lot of methods that makes working with models much easier. However, in some cases I have to implement functionality that is accessing the database, but doesn't return an Eloquent model.

For example, imagine a common User model. Eloquent helps me a lot in CRUD operations for the User model, but what if I have to implement a method that returns some statistics about users (ex. how many total users, how many active users, etc.).

Where should I implement this functionality? In the Eloquent model, as a public static function, (e.g. User::getStats()), or should have a different class for this.

It seems a little bit unnatural to have these methods on a Eloquent model, because they are not Eloquent related.

Answer 1

It's really up to you, but it's a good idea to decide on a convention and stick to it.

My rule of thumb is this:

1. if it's related to a single item that is normally represented by an Eloquent model, do it as a public static method on that model. For example, in my current project, I have a Contract model. I needed a method that would generate a contract ID string (for compatibility with legacy systems) for new contracts. This is related to a single item (a contract) but for technical reasons needed to be generated separately from the Eloquent model (ie. it was based on a separate database with a different connection). So I created a public static method: public static function generateContractIdentifier($id, $salesRep). If you're using a repository for access to your database, you could put it there.

2. If it's more general (ie. not tied to an instance of the Eloquent model), I put it into a separate library. For example, in the same application I have a queue for processing items for printing (an industrial process). The application has a dashboard and I needed to show the current queue status for management. For this I created a ProcessStatus library and implemented a method: public function getStatus() which runs a query and provides the results as an array for display in a view. This is not related to any particular item in the queue, so I put it in a separate library.

Answer 2

As always, it depends on your project and what role the user plays in it.

But basically, no, I don't think the logic for building reports belongs on the user model. While it may be related to the user, regarding the SOLID-principle the User class should only have one responsibility, which in this case is to handle the User entity.

This contains getting and setting properties on an instance, in a simple project it's probably also fine to define some scopes on the model, e.g. to select only active users, like User::getActive();

But as your project grows, you should consider using more specific classes.

For instance, you could abstract the Eloquent functionality into a User-Repository. So now you have a handler for operations on the entitiy itself, like

$userRepo->getAll();
$userRepo->getActive();
$userRepo->getInactive();

and a handler for a User instance:

$user->getName();
$user->setStatus();

Creating reports and statistics is yet a completely different topic. So you could have something like a UserReportBuilder oder UserStatisticsService:

$userStats->getMostActive();
$userStats->getRegistrationsPerDay();

A simple example:

// UserRepository:

class UserRepository
{

  protected $model = $model;

  public function __construct($model)
  {
     // you pass in an instance of the actual Eloquent model
     // so you have the whole power of eloquent in here
     $this->model = $model;
  }

  public function getActive()
  {
     // this returns a collection of models
     return $this->model->where('status', 'active')->get();
  }

}

$userRepo = new UserRepo(new User);

And that's pretty much it. You can still work with Eloquent, but you have separated the functionality in parts with a clewar responsibility. So your UserStats class would only be resposible for building user statistics:

class UserStats
{
    // You could pass in the Repository through the constructor
    // or just use the Eloquent model directly

    public function getRegistrationsPerDay()
    {
        return User::groupBy('day')->get(
            [
                 DB::raw('DATE(created_at) as day'),
                 DB::raw('count(*) as registration_count')
            ]
        );
    }
}

The User instance or the UserStats-builder do not need to know how to fetch all users, and the User instance or the UserRepository do not need to know how to calculate registrations per day, so it makes sense to split that functionality into separate, independent parts that do exactly one thing.

I think you get the idea and I hope it makes sense. Maybe you should make yourself more familiar with the SOLID-principles and try to keep them in mind when you get stuck on problems like that.