DEV Community

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

Posted on

3 1 1 2 1

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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more