[SQL] 어떻게 Laravel 5.1 원시 쿼리를 실행하려면?

어떻게 Laravel 5.1 원시 쿼리를 실행하려면?

그래서 난 내 DB에서 실행이 작은 쿼리를 가지고 있고 그것은 MySQL의 워크 벤치에서 잘 작동합니다. 기본적으로, LEFT와 SELECT는 JOIN과 LEFT와 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();

나는 오류를 받고 있어요

어떻게 Laravel에 완전히 원시 쿼리를 실행하거나 Laravel의 오른쪽 방식으로 쿼리를 작성할 수 있을까?

해결법

1. ==============================

   1.나는이 주제에 내가 코드이의 해결책을 발견 :

   나는이 주제에 내가 코드이의 해결책을 발견 :

   $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");
   

2. ==============================

   2.당신도이 방법으로 같은 원시 쿼리를 실행할 수 있습니다.

   당신도이 방법으로 같은 원시 쿼리를 실행할 수 있습니다.

   DB::table('setting_colleges')->first();
   

from https://stackoverflow.com/questions/33049511/how-to-execute-raw-queries-with-laravel-5-1 by cc-by-sa and MIT license