I want to write 'select' inside 'select' (inner query) in following query. the inner query would be for counting like this: select count('id') from order where memb_id = 2;
and query is:
$payment_pending_report = DB::connection('mysql_freesubs')->table('contact as c')
->select("a.name","c.lname","a.amount as pending amount")
->join("member AS m", "c.id", "=", "m.cont_id")
->join("order AS o", "o.memb_id", "=", "m.id")
->join("account AS a", "c.id", "=", "a.cont_id")
->where("a.amount_type", "=","dr")
->get();
Something like this:
$payment_pending_report = DB::connection('mysql_freesubs')->table('contact as c')
->select("a.name","c.lname","a.amount as pending amount", "oc.OrderCount as order_count")
->join("member AS m", "c.id", "=", "m.cont_id")
->join("order AS o", "o.memb_id", "=", "m.id")
->join("account AS a", "c.id", "=", "a.cont_id")
->join(DB::raw("(SELECT m.id, COUNT(o.some_unique_id) as OrderCount FROM member as m INNER JOIN order as o ON o.memb_id = m.id GROUP BY m.id) AS oc"), function($join)
{
$join->on('m.id', '=', 'oc.id');
})
->join("(")
->where("a.amount_type", "=","dr")
->get();