Adding a subquery to an eloquent query

I want to add a field to the result set of an eloquent query that its value is provided by a subquery.

In other words I want the returned eloquent models have an extra field using that subquery.

SELECT *
FROM (SELECT fair_gift_id,
             (SELECT count(*)
              FROM fair_gift_bus
              WHERE FG.id = fair_gift_id
             ) as bus_count
      FROM fair_gifts as FG,
           fair_gift_bus as FGB
      WHERE FG.fair_code = :fair_code
        AND FGB.bus_code = :member_bus_code
        AND FG.id = FGB.fair_gift_id
      GROUP by fair_gift_id) AS X
WHERE bus_count >= 3;

mattsplat

7 years ago

Level 8

I think addSubSelect() might be what you are looking for. https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

This is an example of how I have used this.

 public function scopeWithLeaderEmail(Builder $query)
    {
        return $query->addSubSelect('leader_email', User::select('users.email')
            ->join('group_user', 'group_user.user_id', '=', 'users.id')
            ->whereColumn('group_id', 'groups.id')
            ->orderBy('group_user.group_leader', 'desc')
        );
    }

jlrdw

7 years ago

Level 75

Or work the examples on eloquent.

https://laravel.com/docs/5.7/eloquent-relationships

You can write queries anyway you need to or want to.

eloquent relations
query builder
db facade
getPdo ()

I've even piggybacked another framework through laravel while developing in laravel, Taylor Made this so flexible.

You can even use the query you have right there just use the DB facade.

See my answer here also

https://laracasts.com/discuss/channels/laravel/laravel-query-builder-error

Please or to participate in this conversation.