# Eloquent Trick: Laravel Model from Subquery

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

```php
class User extends Model {
  protected $table = 'users';
}
```

This approach maps the model directly to a database table. However, there are situations where you might need more complex querying logic, such as filtering, joining, or aggregating data before it's returned. Rather than creating database views, which might not offer the flexibility you need, you can use subqueries within your models.

#### Using Subqueries in Models

Consider a scenario where your `users` table stores information about both admins and regular users. We can create an AdminUser model using a subquery:

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

In this example, the `AdminUser` model is defined to use a subquery that selects only the admin users from the `users` table. The subquery is encapsulated within the model, allowing you to treat it like a standard Eloquent model:

```php
AdminUser::query()->get();
```

This would return all admin users, as defined by the subquery.

or

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

#### Handling Query Limitations

While this approach is powerful, it does come with certain limitations. For instance, using the `find()` method directly on the `AdminUser` model will not work:

```php
AdminUser::query()->find(1); // This will not work
```

This is because `find()` is designed to work with primary keys in a straightforward manner, but our `AdminUser` model is built on a subquery, not a direct table reference.

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

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

This approach ensures that you're still able to retrieve specific records without running into issues.

### Example 2: Subqueries with Joins

Let's take the concept further by considering a more complex scenario. Suppose you want to create a model that retrieves data from multiple tables using a join in the subquery. Imagine you have a `posts` table and a `comments` table, and you want to create a model that retrieves posts along with the number of comments they have.

Here's how you can do it:

```php
class PostWithCommentCount
{
    public function getTable(): string|\Illuminate\Contracts\Database\Query\Expression
    {
        $sql = Post::query()
            ->select('posts.id', 'posts.title', DB::raw('COUNT(comments.id) as comment_count'))
            ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
            ->groupBy('posts.id', 'posts.title')
            ->toRawSql();

        return DB::raw(sprintf('(%s) as posts_with_comment_count', $sql));
    } 
}
```

In this example, the `PostWithCommentCount` model uses a subquery that joins the `posts` and `comments` tables and counts the number of comments for each post. This allows you to query for posts and get their comment counts directly:

```php
$posts = PostWithCommentCount::query()->get();
```

You can even filter posts by comment count if needed:

```php
$popularPosts = PostWithCommentCount::query()->having('comment_count', '>', 10)->get();
```

## Conclusion

Using subqueries in Laravel models is a powerful technique that allows you to encapsulate complex logic within the application layer. This approach not only keeps your models organized and maintainable but also provides the flexibility to adapt to growing complexity in your application. Whether you're dealing with simple filters or complex joins, subqueries give you the tools to manage and extract data efficiently.

As you continue to work with Laravel, consider leveraging subqueries to optimize and simplify your data retrieval logic. They can be a game-changer in maintaining clean and scalable code, especially in large and evolving projects.