How to execute raw queries with Laravel 5.1?
268,826
Solution 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");
Solution 2
DB::statement("your query")
I used it for add index to column in migration
Solution 3
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/
Related videos on Youtube
Comments
-
Sandro Wiggers about 2 years
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?
-
Jimmy Zoto over 8 yearsYou actually don't even need to nest the DB::Raw call. You can just call DB::select("... your query string...");
-
Robert over 7 yearsI 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.
-
ftrotter over 7 yearsby 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.
-
Jabari over 6 yearsYou can bind parameters to raw queries to prevent SQL injections... laravel.com/docs/5.5/database#running-queries
-
Grald almost 5 yearsIt will slow your query it's a bad idea.
-
ali Falahati over 3 yearsCan we paginate data on this query ?
-
Sandro Wiggers over 3 yearsI believe you can by appending a "LIMIT" and "OFFSET" in your raw query -> bitdegree.org/learn/mysql-limit-offset
-
Sandro Wiggers over 3 yearsDont forget to bind parameters to avoid SQL injections as mentioned by stackoverflow.com/users/953833/jabari
-
Root over 3 yearsthis is not a Raw query method in Laravel , it just get the first row of that table
-
Kamlesh about 2 yearsNot worked for me. Thanks.
-
Nilesh Bavliya about 2 yearsThis is the bad query. :(