Larabel 5.1에서 원시 쿼리를 실행하는 방법

Larabel 5.1에서 원시 쿼리를 실행하는 방법

이 작은 쿼리를 DB에서 실행하면 MySQL Workbench에서 정상적으로 작동합니다.기본적으로 왼쪽 조인인 SELECT와 왼쪽 조인인 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

인은 그것을 라라벨에게 번역하려고 했지만 성공하지 못했다.

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

오류가 발생하고 있습니다.

빌더의 ErrorException입니다.php 행 1249: 정의되지 않은 속성:조명\데이터베이스\웅변\빌더: $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");

    DB::statement("your query")

마이그레이션에서 열에 인덱스를 추가하는 데 사용했습니다.

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/

이렇게 원시 쿼리를 실행할 수도 있습니다.

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

언급URL : https://stackoverflow.com/questions/33049511/how-to-execute-raw-queries-with-laravel-5-1