반응형
Laravel 5.1로 원시 쿼리를 실행하는 방법은 무엇입니까?
그래서이 작은 쿼리를 내 DB에서 실행할 수 있으며 MySQL Workbench에서 잘 작동합니다. 기본적으로 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 ASC
그것을 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();
오류가 발생합니다
Builder.php 줄 1249의 ErrorException : 정의되지 않은 속성 : Illuminate \ Database \ Eloquent \ Builder :: $ bindings
Laravel에서 완전히 원시 쿼리를 실행하거나 Laravel에서 올바른 방식으로 쿼리를 작성하려면 어떻게해야합니까?
이 주제 에서 해결책을 찾았고 다음을 코딩합니다.
$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");
참조 URL : https://stackoverflow.com/questions/33049511/how-to-execute-raw-queries-with-laravel-5-1
반응형
'programing' 카테고리의 다른 글
Intent.FLAG_ACTIVITY_CLEAR_TASK와 Intent.FLAG_ACTIVITY_TASK_ON_HOME의 차이점 (0) | 2021.01.18 |
---|---|
Jenkins 콘솔 출력에서 에코 끄기 (0) | 2021.01.18 |
Express 미들웨어의 req.locals vs. res.locals vs. res.data vs. req.data vs. app.locals (0) | 2021.01.18 |
Laravel 5.1을 5.2로 업그레이드하는 동안 치명적인 오류 (0) | 2021.01.18 |
Redux에 여러 미들웨어를 추가하는 방법은 무엇입니까? (0) | 2021.01.18 |