How to execute raw queries with Laravel 5.1?

Viewed 79 times

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: IlluminateDatabaseEloquentBuilder::$bindings

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

Answers

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

Wednesday, October 19, 2022

shepoooo_19-6ab304f5062d

To list out the tables in database you can do

$tables = DB::select('SHOW TABLES');
foreach($tables as $table)
{
      echo $table->Tables_in_db_name;
}

You'll have to change the db_name to the name of your database.

EDIT : FOR LIKE CASES

foreach ($tables as $table) {
    foreach ($table as $key => $value)
        echo $value;
}

Thursday, August 18, 2022

tommy

Try something like this

DB::select('exec my_stored_procedure("Param1", "param2",..)');

DB::select('exec my_stored_procedure(?,?,..)',array($Param1,$param2));

Try this for without parameters

DB::select('EXEC my_stored_procedure')

Saturday, December 10, 2022

jon_custer

There are date helpers available in the query builder:

$post = Mjblog::whereYear('created_at', '=', $year)
              ->whereMonth('created_at', '=', $month)
              ->get();

Saturday, December 17, 2022

nereis

My question got answered on github.

The solution is to wrap the execute in a transaction context:

with engine.begin() as conn:
   conn.execute("whatever")

Sunday, December 11, 2022

fabio_manzo

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

sql

database

laravel-5.1

How to execute raw queries with Laravel 5.1?

Answers

php,mysql,sql,database,laravel-5.1