I tried to follow a serie to get in report the revenue between 2 dates where order status completed and paid and I get this error:
Illuminate \ Database \ QueryException (42000)
SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe près de 'recursive date_ranges AS ( SELECT ? AS date UNION ALL SELECT date + INT' à la ligne 1
Previous exceptions
SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe près de 'recursive date_ranges AS ( SELECT ? AS date UNION ALL SELECT date + INT' à la ligne 1 (42000)
Report Controller: Revenue function:
public function revenue(Request $request)
{
$this->data['startDate'] = $startDate;
$this->data['endDate'] = $endDate;
$sql = "WITH recursive date_ranges AS (
SELECT :start_date_series AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM date_ranges
WHERE date < :end_date_series
),
filtered_orders AS (
SELECT *
FROM orders
WHERE DATE(order_date) >= :start_date
AND DATE(order_date) <= :end_date
AND status = :status
AND payment_status = :payment_status
)
SELECT
DISTINCT DR.date,
COUNT(FO.id) num_of_orders,
COALESCE(SUM(FO.grand_total),0) gross_revenue,
COALESCE(SUM(FO.tax_amount),0) taxes_amount,
COALESCE(SUM(FO.shipping_cost),0) shipping_amount,
COALESCE(SUM(FO.grand_total - FO.tax_amount - FO.shipping_cost - FO.discount_amount),0) net_revenue
FROM date_ranges DR
LEFT JOIN filtered_orders FO ON DATE(order_date) = DR.date
GROUP BY DR.date
ORDER BY DR.date ASC";
$revenues = \DB::select(
\DB::raw($sql),
[
'start_date_series' => $startDate,
'end_date_series' => $endDate,
'start_date' => $startDate,
'end_date' => $endDate,
'status' => Order::COMPLETED,
'payment_status' => Order::PAID,
]
);
}
return view('admin.reports.revenue', $this->data);
}
so I want to change this code to work without error:( I use mysql 5.7 and not mysql 8):
$sql = "WITH recursive date_ranges AS (
SELECT :start_date_series AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM date_ranges
WHERE date < :end_date_series
),
Hmmm…I’ve never seen it marked up like that. Seems to me the syntax is you have two “queries” there in one SQL statement being parsed. My “guess” is the syntax of the date in the date range, but it could be a quick guess since I’ve never seen that syntax applied like that - it could be
But why are you building that query like that at all? It looks like it could be simplified greatly…
Note: The distinct isn’t needed when using a group by
SELECT DR.date
, COUNT(FO.id) AS num_of_orders
, COALESCE(SUM(FO.grand_total),0) AS gross_revenue
, COALESCE(SUM(FO.tax_amount),0) AS taxes_amount
, COALESCE(SUM(FO.shipping_cost),0) AS shipping_amount
, COALESCE(SUM(FO.grand_total - FO.tax_amount - FO.shipping_cost - FO.discount_amount),0) AS net_revenue
FROM (SELECT :start_date_series AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM date_ranges
WHERE date < :end_date_series) DR
LEFT JOIN orders FO ON DATE(order_date) = DR.date
AND status = :status
AND payment_status = :payment_status
GROUP BY DR.date
ORDER BY DR.date ASC
Thank you veeeeeery much DaveMaxwell
date_ranges it’s not a table so what to do to not get error that “date_ranges table doesn’t exist”
and if you want I will give you from where I follow this code(github and youtube) and thank you very much and sorry because I try to learn and there’s a lot of things that I don’t understand it correctly and thank you very much
The date range table is there to show all the possible dates (I’m assuming populated by some kind of trigger on the orders table. It’s there for this exercise to allow you to show a date and if orders exist for that date, or zeroes if it doesn’t. So to get the query to run as needed, you’re going to have to create a date_ranges table. If you don’t have it or can’t create it, then you’re going to change the way the query works and it’ll show you just the dates that have orders.
SELECT order_date
, COUNT(*) AS num_of_orders
, SUM(grand_total) AS gross_revenue
, SUM(tax_amount) AS taxes_amount
, SUM(shipping_cost) AS shipping_amount
, SUM(grand_total - tax_amount - shipping_cost - discount_amount) AS net_revenue
FROM orders
WHERE DATE(order_date) >= :start_date
AND DATE(order_date) <= :end_date
AND status = :status
AND payment_status = :payment_status
GROUP BY order_date
ORDER BY order_date ASC
thank you very much but I get this error SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (HY093)
and I have to use date ranges forexemple when I choose 1-11-2021 and 30-11-2021 in date so I just need to show orders with status paid and completed this code to choose betwwen dates
$startDate = $request->input('start');
$endDate = $request->input('end');
if ($startDate && !$endDate) {
\Session::flash('error', 'The end date is required if the start date is present');
return redirect('admin/reports/revenue');
}
if (!$startDate && $endDate) {
\Session::flash('error', 'The start date is required if the end date is present');
return redirect('admin/reports/revenue');
}
if ($startDate && $endDate) {
if (strtotime($endDate) < strtotime($startDate)) {
\Session::flash('error', 'The end date should be greater or equal than start date');
return redirect('admin/reports/revenue');
}
$earlier = new \DateTime($startDate);
$later = new \DateTime($endDate);
$diff = $later->diff($earlier)->format("%a");
if ($diff >= 31) {
\Session::flash('error', 'The number of days in the date ranges should be lower or equal to 31 days');
return redirect('admin/reports/revenue');
}
} else {
$currentDate = date('Y-m-d');
$startDate = date('Y-m-01', strtotime($currentDate));
$endDate = date('Y-m-t', strtotime($currentDate));
}
$this->data['startDate'] = $startDate;
$this->data['endDate'] = $endDate;
This is one of the problems I find with most online tutorials. They give code but don’t tell you why.
The reason you’re getting the error is because we changed the defined parameters in the query and two of them don’t exist anymore. You would need to change the call to this to account for the defined parameters: