If yopu want to use raw SQL you can use DB::raw() or DB::unprepared()
However another option would be to create a "view" in the database and use your raw SQL as the view definition, then create a model and use the view as it's table. This method will be better for selecting data but you will need special insert and update functions if you go this route.
IMO creating the view is the more elegant solution.
Thanks, I've looked into using views - and will probably implement those as an after-sight. Is there a means to achieve distinguishing tables using eloquent in this way however? I will use raw SQL to patch this for now, but I'm sure there should be a way to do this using eloquent.
For anyone having the same problem in future. I managed to solve this by adding an additional static 'type' record to the select query.
You can see this in use at the end of the select statements in the following:
$user_items = DB::table('user_items')
->select(DB::raw('user_items.id, user_items.item_name, user_items.item_description, "userItem" as type'))
->where('user_items.item_name', 'LIKE', $search)
->orWhere('user_items.item_description', 'LIKE', $search);
$store_items = DB::table('store_items')
->select(DB::raw('store_items.id, store_items.item_name, store_items.item_name, "storeItem" as type'))
->where('store_items.item_name', 'LIKE', $search)
->orWhere('store_items.item_description', 'LIKE', $search);
$results = $user_items->union($store_items)->take(30)->get();
// 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