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
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.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community