Where do we have to write queries in Laravel: in model, controller or routes?

Question

I'm little confused with the database queries in the laravel, where do we have to write our queries: in controllers, models or routes ?

I've been through many tutorials and I see so much difference. Just creating confusion.

Kindly explain somebody

Answer 1

It depends on different factors, but in short you can write them both in Model, Controllers or Repositories

If you're writing a controller action and you need a query that you'll use only once, it's perfectly fine to write the query directly in the controller (or even in the route's closure ).

For example, if you want to get all users of type admin:

$admins = User::where('type', 'admin')->get();

Now, suppose you need to get the admins in more than one controller method; instead of rewriting the same query, you can create a Repository class to wrap the access to the users' Model and write the query inside the Repository:

class UserRepository
{
    public function getAllAdmins()
    {
        return User::where('type', 'admin')->get();
    }
}

Now in your controllers you can inject the Repository and use the same method of the Repository to get the admin users: this will keep your code DRY as you don't have to repeat the same query among the controllers' actions

Controller

public function __construct(UserRepository $userRepo)
{
     $this->userRepo = $userRepo;
} 

//controller action
public function index()
{
     $admins = $this->userRepo->getAllAdmins(); 
}

Finally, let's suppose you need a query to count the number of the admin users. You could write this query in the UserRepository:

public function getAdminNum()
{
    return User::where('type', 'admin')->count();    
}

And it would be ok, but we can note that the User::where('type', 'admin') fragment of the query is shared with the query in getAllAdmins So we can improve this by using query scopes :

User Model

public function scopeAdmins($query)
{
    return $query->where('type', 'admin');    
}

by this, in the UserRepository methods we can rewrite our previous queries as:

public function getAllAdmins()
{
    return User::admins()->get();
} 

public function getAdminNum()
{
    return User::admins()->count();  
}

And i've just showed you a case in which a query would be writed inside a Model