DEV Community

Cover image for Eloquent Trick: Laravel Model from Subquery
Bedram Tamang
Bedram Tamang

Posted on

Eloquent Trick: Laravel Model from Subquery

In Laravel, it's common to define a model's corresponding table using the table property, such as:

class User extends Model {
  protected $table = 'users';
}
Enter fullscreen mode Exit fullscreen mode

However, instead of mapping directly to a table, we can use a subquery. This technique allows us to encapsulate complex queries within the application layer, much like creating a database view, but with the added flexibility of Eloquent operations.

For instance, consider a users table that stores information about both admins and regular users. We can create an AdminUser model using a subquery:

class AdminUser
{
    public function getTable(): string|\Illuminate\Contracts\Database\Query\Expression
    {
        $sql = User::query()
            ->select('id', 'name')
            ->where('admin_user', true)
            ->toRawSql();

        return DB::raw(sprintf('(%s) as admin_users', $sql));
    } 
}
Enter fullscreen mode Exit fullscreen mode

This model pulls data from a subquery (select id, name from users where admin_user = 1), allowing you to query it just like a standard model:

AdminUser::query()->get();
Enter fullscreen mode Exit fullscreen mode

or

AdminUser::query()->first();
Enter fullscreen mode Exit fullscreen mode

However, be aware that certain queries, like find(1), won't work directly:

AdminUser::query()->find(1);
Enter fullscreen mode Exit fullscreen mode

To work around this, you can use a where condition combined with first():

AdminUser::query()->where('id', 1)->first();
Enter fullscreen mode Exit fullscreen mode

Conclusion

The example provided is a straightforward illustration of how we can structure logic within a model. However, this approach can be scaled and adapted to accommodate much more complex scenarios. By leveraging such structures, we can efficiently manage and extract intricate logic, ensuring that our models remain organized, maintainable, and scalable as they grow in complexity.

Top comments (0)