convert subselect sql query to laravel query

Question

i have a mysql query below

 select * from
  ( SELECT *,(select count(*) from `comments` where parent_id=b._id) as 
  cnt FROM `comments` b )x 
  where ((x.type_user='xxxx' and (cnt>0 or x.is_starter=1)) 
  or(type_user='user' and cnt>=0)) 
  and deleted_at is null and parent_id is null  order by created_at desc

i want to convert this to laravel query.this is what i try

  $res=DB::table('comments')
        ->select(DB::raw('comments.*, (select count(*) from `comments` b where b.parent_id=comments._id) as cnt'));

        $res->where(function ($query) {
            $query->where('comments.type_user','xxxx')
            ->where(function ($query1) {
                $query1->where('cnt','>',0)
                ->orWhere('comments.is_starter',1);
            });
        })->orWhere(function($query) {
            $query
            ->where('comments.type_user','user')
            ->where('cnt', '>=',0); 
        });

result in the error below

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cnt' in 'where clause'

please help.thanks in advance

yes .please convert that to laravel query . i am a newbie in laravel — user4206843
problem is the outer select. i don't know how to integrate that to laravel — user4206843
Is there a reason you don't want to use laravel eloquent or its also an option? — AntonyMN

Jonas Staudenmeir Jonas Staudenmeir · Accepted Answer · 2018-05-30T13:48:33

Laravel 5.6:

$res = DB::query()
    ->fromSub(function($query) {
        $query->select('*', DB::raw('(select count(*) from `comments` where parent_id=b._id) as cnt'))
            ->from('comments as b');
    }, 'x');

Laravel <5.6:

$res = DB::query()
    ->from(DB::raw('(SELECT *, (select count(*) from `comments` where parent_id=b._id) as cnt FROM `comments` b) x'));

convert subselect sql query to laravel query

1 Answers