// Use PDO . It is the simplest answer I found after two days of struggling for using complex UNION in Laravel
$PDO = DB::connection('mysql')->getPdo();
$billingStmt = $PDO->prepare("
select * from (SELECT *
FROM t_statements
WHERE reference_id = $user_id
AND service_provider='FOLDER'
AND bill_name IS NOT NULL
ORDER BY bill_name ASC ) AS a
UNION ALL
SELECT *
FROM (
SELECT *
FROM t_statements
WHERE reference_id = $user_id
AND service_provider !='FOLDER'
AND bill_name IS NOT NULL
ORDER BY (CASE WHEN is_paid = 0 THEN due_date ELSE is_paid END) DESC) b
");
$billingStmt->execute();
$usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
header('Content-Type: application/json');
$androidUserBills = json_encode($usersBills); // return results as json
return response($androidUserBills);
// JSON response
[
{
"id": "247",
"created_at": "2016-02-23 10:44:33",
"updated_at": "2016-02-23 16:58:57",
"t_user_account_id": "245",
"statement_date": null,
"due_date": "0000-00-00 00:00:00",
"amount": "0",
"is_paid": "0",
"is_reminded": "1",
"overdue": null,
"current_amount": null,
"bill_total": "88.5",
"bill_id": "zd91NwGU",
"bill_name": "Utility",
"predecessor": null,
"reference_id": "120",
"service_provider": "FOLDER",
"sp_id": null
},
{
"id": "252",
"created_at": "2016-02-23 16:29:50",
"updated_at": "2016-02-23 16:58:25",
"t_user_account_id": "250",
"statement_date": null,
"due_date": "2016-03-04 17:52:34",
"amount": "0",
"is_paid": "0",
"is_reminded": "1",
"overdue": null,
"current_amount": null,
"bill_total": "88.5",
"bill_id": "Lojnc",
"bill_name": "Water bill",
"predecessor": null,
"reference_id": "120",
"service_provider": "IWK",
"sp_id": "7"
}
]
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community