select statement inside another select

@KifayatMsd Check this package https://github.com/jarektkaczyk/eloquence - it provides Subquery helper class for such cases. Then you need something like this:

// assuming you have an eloquent model Ticket, for presentation
$countTickets = new \Sofa\Eloquence\Subquery(
    Ticket::from('dental_support_tickets as t')
        ->selectRaw('count(t.id)')->whereRaw('t.category_id=c.id')->where('t.status', '0'),
    'num_tickets' // alias
);

// now let's use query builder
$countAdmins = new \Sofa\Eloquence\Subquery(
    DB::table('dental_support_category_admin as a')
        ->selectRaw('count(a.id)')->whereRaw('a.category_id=c.id'), 
    'num_admins' // alias
);

// finally eloquent result
Category::from('dental_support_categories as c')
        ->select('c.*', $countTickets, $countAdmins)
        ->addBinding($countTickets->getBindings(), 'select')
        ->where('c.status', 0)
        ->orderBy('c.title', 'asc')
        ->paginate()

It will automatically build the query for you:

select 
    c.*, 
    (select count (t.id) from dental_support_tickets t where t.category_id=c.id and t.status = ?) as num_tickets,
    (select count(a.id) from dental_support_category_admin a where a.category_id=c.id) as num_admins 
from dental_support_categories c 
where c.status = ? 
order by c.title ASC

I'd go with \DB::raw(); http://laravel.com/docs/5.0/queries#raw-expressions

@phildawson I tried it using \DB::raw() but still no joy.

$categories = \DB::table('dental_support_categories as c') ->select(\DB::raw('(COUNT(t.id) FROM dental_support_tickets t WHERE t.category_id=c.id AND t.status=0) AS num_tickets')) ->where('c.status', 0) ->orderBy('c.title','ASC') ->get();

@KifayatMsd How about?

$categories = \DB::table('dental_support_categories AS c')
    ->select(\DB::raw('c.*, (SELECT COUNT(t.id) FROM dental_support_tickets t WHERE t.category_id=c.id AND t.status=0) AS num_tickets, (SELECT COUNT(a.id) FROM dental_support_category_admin a WHERE a.category_id=c.id) AS num_admins'))
    ->where('c.status', 0)
    ->orderBy('c.title')
    ->get();

The worst thing is that you have to think how it works, but not as it should be. Please replace Eloquence to someone more reliable.

You can write select subquery this way

return Destination::addSelect(['last_flight' => Flight::select('name')
    ->whereColumn('destination_id', 'destinations.id')
    ->orderBy('arrived_at', 'desc')
    ->limit(1)
])->get();

Refer: https://laravel-news.com/eloquent-subquery-enhancements