Support the ongoing development of Laravel.io →
posted 8 years ago
Database
Last updated 6 months ago.
0

Why not just run the whole thing as a RAW query?

Last updated 6 months ago.
0

I was having problems with SQL queries and kept being told that I needed to use raw queries but still couldn't figure out the syntax. What I found was that you can use regular SQL queries using the following construct -

$success = DB::statement('string containing a regular SQL command');

so I think your query would work in its original form if you just wrap in in the DB::statement command like this:

$success = DB::statement('SELECT SUM(transactions.amount) AS total, products.name 
FROM transactions, product_stock, product_catalog, products
WHERE transactions.id_product_stock = product_stock.id_prodct_stock
AND product_stock.id_product_catalog = product_catalog.id_product_catalog
AND product_catalog.id_product = products.id_produto
GROUP BY (products.name)');

I have found that some SQL commands don't work with DB:statement for some reason, but DB::select seems to work in place of DB::statement in those situations.

The examples I found when trying to figure this out only showed very simple queries like dropping a table, but I found that they seem to work very well with more complex queries including ones that need to utilize more than one table and more than one database.

Last updated 6 months ago.
0

darrencraig said:

Why not just run the whole thing as a RAW query?

I tried and returns empty, but my tables have data:

DB::raw('select SUM(transactions.amount) AS total, products.name 
from transactions, product_stock, product_catalog, products
where transactions.id_product_stock = product_stock.id_prodct_stock
and product_stock.id_product_catalog = product_catalog.id_product_catalog
and product_catalog.id_product = products.id_produto
group by (products.name)');

GSheffield said:

I was having problems with SQL queries and kept being told that I needed to use raw queries but still couldn't figure out the syntax. What I found was that you can use regular SQL queries using the following construct -

$success = DB::statement('string containing a regular SQL command');

so I think your query would work in its original form if you just wrap in in the DB::statement command like this:

$success = DB::statement('SELECT SUM(transactions.amount) AS total, products.name FROM transactions, product_stock, product_catalog, products WHERE transactions.id_product_stock = product_stock.id_prodct_stock AND product_stock.id_product_catalog = product_catalog.id_product_catalog AND product_catalog.id_product = products.id_produto GROUP BY (products.name)');

I have found that some SQL commands don't work with DB:statement for some reason, but DB::select seems to work in place of DB::statement in those situations.

The examples I found when trying to figure this out only showed very simple queries like dropping a table, but I found that they seem to work very well with more complex queries including ones that need to utilize more than one table and more than one database.

I changed the DB::raw to DB::statement and this error appears: "Invalid argument supplied for foreach()", I tried to write the values on the view by foreach.

Last updated 6 months ago.
0

Did you try to use DB:statement($query); command?

Last updated 6 months ago.
0

intrip said:

Did you try to use DB:statement($query); command?

Yes and this error appears: "Invalid argument supplied for foreach()". I tried to write the values on the view by foreach.

Last updated 6 months ago.
0

I make to return the expected values with DB::select('select.."). Thanks GSheffield​​, but I need one of the values ​​as integer and returns a string :s

Last updated 6 months ago.
0

I solved the problem with this:

Transaction::select(DB::raw('SUM(transaction.amount AS total', 'products.name AS name'))
->join('product_stock', 'transactions.id_product_stock', '=', 'product.stock.id_product_stock')
->join('product_catalog', 'product_stock.id_product_catalog', '=', 'product_catalog.id_product_catalog')
->join('products', 'product_catalog.id_product', '=', 'products.id_product')
->groupBy('products.name')
->get();

Thanks to everyone.

Last updated 6 months ago.
0

Sign in to participate in this thread!

LaraJobs

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2022 Laravel.io - All rights reserved.