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

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.

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.

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

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.

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