Eloquent Trick: Laravel Model from Subquery

#laravel #eloquent #php #database

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';
}

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));
    } 
}

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();

AdminUser::query()->first();

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

AdminUser::query()->find(1);

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

AdminUser::query()->where('id', 1)->first();

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.