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?

score 224 · Accepted Answer · edited Nov 02 '18 at 19:51

224

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

edited Nov 02 '18 at 19:51

Adrian Enriquez

8,175
7
45
64

answered Oct 10 '15 at 02:18

Sandro Wiggers

4,440
3
20
25

6

You actually don't even need to nest the DB::Raw call. You can just call DB::select("... your query string..."); – Jimmy Zoto Oct 10 '15 at 02:50
4

I have a question if I may. Does using raw sql queries via query builder(with or without DB::raw) in Laravel 5 (5.3 to be exact) is enough to prevent sql injection? I have found some article about it but it's for Laravel 4. I can't find one convincing confirmation. – Robert Nov 25 '16 at 13:44
2

by itself, DB::select does not protect against SQL injection. However, by ensuring that all user-provided input has been run through $safe_string = DB::connection()->getPdo()->quote($string) you can protect your code. – ftrotter Jan 12 '17 at 10:27
6

You can bind parameters to raw queries to prevent SQL injections... https://laravel.com/docs/5.5/database#running-queries – Jabari Jan 04 '18 at 18:26
It will slow your query it's a bad idea. – Grald Aug 01 '19 at 09:02
Can we paginate data on this query ? – ali Falahati Sep 08 '20 at 06:12
I believe you can by appending a "LIMIT" and "OFFSET" in your raw query -> https://www.bitdegree.org/learn/mysql-limit-offset – Sandro Wiggers Sep 28 '20 at 23:27
Dont forget to bind parameters to avoid SQL injections as mentioned by https://stackoverflow.com/users/953833/jabari – Sandro Wiggers Sep 28 '20 at 23:27

score 51 · Answer 2 · edited Jun 15 '20 at 10:46

51

    DB::statement("your query")

I used it for add index to column in migration

edited Jun 15 '20 at 10:46

sonique

4,539
2
30
39

answered Jun 15 '20 at 09:41

ДароваДарова

527
4
2

score 3 · Answer 3 · answered Feb 22 '22 at 10:55

Example from Laravel documentation:

$users = DB::table('users')
    ->selectRaw('count(*) as user_count, status')
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

Another example:

$products = DB::table('products')
    ->leftjoin('category','category.product_id','=','products.id')
    ->selectRaw('COUNT(*) as nbr', 'products.*')
    ->groupBy('products.id')
    ->get();

Another example – we can even perform avg() and count() in the same statement.

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

Credits: https://blog.quickadminpanel.com/5-ways-to-use-raw-database-queries-in-laravel/

score 0 · Answer 4 · answered Dec 20 '22 at 14:11

0

Also, you can use

DB::unprepared()

answered Dec 20 '22 at 14:11

Martin Tonev

747
12
21

score -30 · Answer 5 · answered Feb 27 '20 at 16:36

-30

you can run raw query like this way too.

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

answered Feb 27 '20 at 16:36

pankaj

1
17
36

5

this is not a Raw query method in Laravel , it just get the first row of that table – Root Jan 05 '21 at 17:27
This is the bad query. :( – Nilesh Bavliya Feb 21 '22 at 10:57

How to execute raw queries with Laravel 5.1?

5 Answers5

Linked

Related