How to execute raw queries with Laravel 5.1?

Question

So I have this tiny query to run on my DB and it works fine in MySQL Workbench. Basically, a SELECT with LEFT JOIN and UNION with LEFT JOIN again.

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 

In tried to translate it to Laravel, with no success.

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

I'm getting the error

ErrorException in Builder.php line 1249: Undefined property: Illuminate\Database\Eloquent\Builder::$bindings

How could I execute a completely raw query in Laravel or write the query in the right manner in Laravel?

Answer 1

I found the solution in this topic and I code this:

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