如何使用Laravel 5.1执行原始查询？
如何使用Laravel 5.1执行原始查询？
提问于 2015-10-10 10:07:47
所以我在我的数据库上运行了这个小查询，它在MySQL工作台中运行得很好。基本上，LEFT JOIN的SELECT和LEFT JOIN的UNION。
SELECT
cards.id_card,
cards.hash_card,
cards.`table`,
users.name,
0 as total,
cards.card_status,
cards.created_at
FROM cards
LEFT JOIN users
ON users.id_user = cards.id_user
WHERE hash_card NOT IN ( SELECT orders.hash_card FROM orders )
UNION
SELECT
cards.id_card,
orders.hash_card,
cards.`table`,
users.name,
sum(orders.quantity*orders.product_price) as total,
cards.card_status,
max(orders.created_at)
FROM menu.orders
LEFT JOIN cards
ON cards.hash_card = orders.hash_card
LEFT JOIN users
ON users.id_user = cards.id_user
GROUP BY hash_card
ORDER BY id_card ASCIn试图将其翻译成Laravel，但没有成功。
$cards = Card::selectRaw('cards.id_card, cards.hash_card ,cards.table, users.name, 0 as total, cards.card_status, cards.created_at as last_update')
->leftJoin('users','users.id_user','=','cards.id_user')
->whereNotIn( 'hash_card', Order::select('orders.hash_card')->get() )
->union(
Order::selectRaw('cards.id_card, orders.hash_card, cards.table, users.name, sum(orders.quantity*orders.product_price) as total, cards.card_status, max(orders.created_at) as last_update')
->leftJoin('cards','cards.hash_card','=','orders.hash_card')
->leftJoin('users','users.id_user','=','cards.id_user')
)
->groupBy('hash_card')
->orderBy('cards.id_card','asc')
->get();我得到了一个错误
Illuminate\Database\Eloquent\Builder::$bindings行中的
ErrorException :未定义的属性: Builder.php
如何在Laravel中执行完全原始的查询，或者在Laravel中以正确的方式编写查询？
回答 4
Stack Overflow用户
回答已采纳
发布于 2015-10-10 10:18:41
我在this topic中找到了解决方案，并编写了以下代码：
$cards = DB::select("SELECT
cards.id_card,
cards.hash_card,
cards.`table`,
users.name,
0 as total,
cards.card_status,
cards.created_at as last_update
FROM cards
LEFT JOIN users
ON users.id_user = cards.id_user
WHERE hash_card NOT IN ( SELECT orders.hash_card FROM orders )
UNION
SELECT
cards.id_card,
orders.hash_card,
cards.`table`,
users.name,
sum(orders.quantity*orders.product_price) as total,
cards.card_status,
max(orders.created_at) last_update
FROM menu.orders
LEFT JOIN cards
ON cards.hash_card = orders.hash_card
LEFT JOIN users
ON users.id_user = cards.id_user
GROUP BY hash_card
ORDER BY id_card ASC");Stack Overflow用户
发布于 2020-06-15 17:41:45
DB::statement("your query")我使用它在迁移中向列添加索引
Stack Overflow用户
发布于 2022-02-22 10:55:59
来自Laravel文档的示例：
$users = DB::table('users')
->selectRaw('count(*) as user_count, status')
->where('status', '<>', 1)
->groupBy('status')
->get();另一个例子：
$products = DB::table('products')
->leftjoin('category','category.product_id','=','products.id')
->selectRaw('COUNT(*) as nbr', 'products.*')
->groupBy('products.id')
->get();另一个例子-我们甚至可以在同一个语句中执行avg()和count()。
$salaries = DB::table('salaries')
->selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count')
->join('companies', 'salaries.company_id', '=', 'companies.id')
->groupBy('companies.id')
->orderByDesc('avg_salary')
->get();致词：https://blog.quickadminpanel.com/5-ways-to-use-raw-database-queries-in-laravel/
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33049511
复制相关文章
相似问题