CODEWITHSUNDEEP
phplaravel
Төгөлдөр Төрбат
Төгөлдөр Төрбат

Reputation: 77

How to do this in laravel, Sub select

How to make this query in laravel

SELECT count(*)
  FROM (SELECT *
          FROM REPORT_INBOUND
         WHERE ul_success_inbound = 0
           AND moc_voice_records = 0
           AND regdate = '2019-06-13')
 WHERE gprs_records = 0
   AND moc_sms_records = 0;

And i wrote this query in laravel

$moc = DB::table('REPORT_INBOUND')
        ->select('REPORT_INBOUND.*',
            DB::raw("select *
                       from REPORT_INBOUND
                      where UL_SUCCESS_INBOUND = 0
                        and MOC_VOICE_RECORDS = 0"))
        ->where([['gprs_records', '=', 0], ['moc_sms_records', '=', 0]]);

But it returned it. I think it's wrong result

{"connection":{},"grammar":{},"processor":{},"bindings":{"select":[],"join":[],"where":[0,0],"having":[],"order":[],"union":[]},"aggregate":null,"columns":["REPORT_INBOUND.*",{}],"distinct":false,"from":"REPORT_INBOUND","joins":null,"wheres":[{"type":"Nested","query":{"connection":{},"grammar":{},"processor":{},"bindings":{"select":[],"join":[],"where":[0,0],"having":[],"order":[],"union":[]},"aggregate":null,"columns":null,"distinct":false,"from":"REPORT_INBOUND","joins":null,"wheres":[{"type":"Basic","column":"gprs_records","operator":"=","value":0,"boolean":"and"},{"type":"Basic","column":"moc_sms_records","operator":"=","value":0,"boolean":"and"}],"groups":null,"havings":null,"orders":null,"limit":null,"offset":null,"unions":null,"unionLimit":null,"unionOffset":null,"unionOrders":null,"lock":null,"operators":["=","<",">","<=",">=","<>","!=","<=>","like","like binary","not like","ilike","&","|","^","<<",">>","rlike","regexp","not regexp","~","~*","!~","!~*","similar to","not similar to","not ilike","~~*","!~~*"],"useWritePdo":false},"boolean":"and"}],"groups":null,"havings":null,"orders":null,"limit":null,"offset":null,"unions":null,"unionLimit":null,"unionOffset":null,"unionOrders":null,"lock":null,"operators":["=","<",">","<=",">=","<>","!=","<=>","like","like binary","not like","ilike","&","|","^","<<",">>","rlike","regexp","not regexp","~","~*","!~","!~*","similar to","not similar to","not ilike","~~*","!~~*"],"useWritePdo":false}

Upvotes: 0

Views: 100

Answers (2)

Matius Nugroho Aryanto
Matius Nugroho Aryanto

Reputation: 901

your query is correct, you just need to call ->get()

$moc = DB::table('REPORT_INBOUND')
        ->select('REPORT_INBOUND.*',
            DB::raw("select *
                       from REPORT_INBOUND
                      where UL_SUCCESS_INBOUND = 0
                        and MOC_VOICE_RECORDS = 0"))
        ->where([['gprs_records', '=', 0], ['moc_sms_records', '=', 0]])->get()

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

I don't even think that you need a subquery here, the following should work:

$count = DB::table('REPORT_INBOUND')
    ->where('UL_SUCCESS_INBOUND', '=', 0)
    ->where('MOC_VOICE_RECORDS', '=', 0)
    ->where('regdate', '=', '2019-06-13')
    ->where('gprs_records', '=', 0)
    ->where('moc_sms_records', '=', 0)
    ->count();

That is, I am suggesting that you just run the following raw MySQL query:

SELECT COUNT(*)
FROM REPORT_INBOUND
WHERE
    ul_success_inbound = 0 AND
    moc_voice_records = 0 AND
    regdate = '2019-06-13' AND
    gprs_records = 0 AND
    moc_sms_records = 0;

Upvotes: 1

Related Questions