Level 73
You can use DB::select to do that.
https://laravel.com/docs/8.x/database#running-a-select-query
May 18, 2021
15
Level 1
I need help to convert this SQL query to Eloquent
This query is meant to get the sum of all adult_rates in the given date range (from start_date to end_date)
declare @startdate date, @enddate date
set @startdate = '20150101'
set @enddate = '20150105'
select
sum(datediff(
day,
case when @startdate > start_date then @startdate else start_date end,
case when @enddate < end_date then @enddate else end_date end)
* adult_rate)
from
reservation
where
end_date >= @startdate and
start_date <= @enddate
Level 1
thanks,
this is what I could come up with, but it doesn't seem to do the same thing, PS. I'm new to Eloquent queries
DB::table("rates")
->selectRaw("sum (datediff( day, case when $start_date > start_date then $start_date else start_date end, case when $end_date < end_date then $end_date else end_date end) * adult_rate)")
->where("end_date", ">=", $start_date)
->where("start_date", "<=", $end_date)
->get();
Level 55
@jroid try to modify your query to something like
DB::table("rates")
->selectRaw("sum (datediff( day, case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value", [$start_date, $start_date, $end_date, $end_date])
->where("end_date", ">=", $start_date)
->where("start_date", "<=", $end_date)
->value('my_value');
Level 1
thanks @silencebringer used your query and am still getting the error below:
pointing to the DATEDIFF should not have more than two parameters
"message": "SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'datediff' (SQL: select sum (datediff( day, case when 2021-05-26 > start_date then 2021-05-26 else start_date end, case when 2021-05-27 < end_date then 2021-05-27 else end_date end) * adult_rate) as my_value from `rates` where `end_date` >= 2021-05-26 and `start_date` <= 2021-05-27 limit 1)",
Level 55
@jroid which DB_CONNECTION do you use?
datediff accept 2 params for mysql and 3 params for sqlsrv
Level 1
@silencebringer I'm using mysql
Level 55
@jroid this way just remove day from datediff
DB::table("rates")
->selectRaw("sum (datediff(case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value", [$start_date, $start_date, $end_date, $end_date])
->where("end_date", ">=", $start_date)
->where("start_date", "<=", $end_date)
->value('my_value');
Level 1
@silencebringer its still not working 😭😭😭😭
error now says sum does not exist referring to it as a table
Level 39
Eloquent will then retransform your query in sql, that decreases performance to use it so it's better to use db facade.
$startdate= '20150101';
$enddate='20150105'';
\DB::select("select
sum(datediff(
day,
case when $startdate > start_date then $startdate else start_date end,
case when $enddate < end_date then $enddate else end_date end)
* adult_rate)
from
reservation
where
end_date >= $startdate and
start_date <= $enddate");
Level 55
@jroid ummm... I see that your table name is different to the original post. SHould it be rates or reservations?
And - let's try to dump your query and see what we have:
DB::table("rates")
->selectRaw("sum (datediff(case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value", [$start_date, $start_date, $end_date, $end_date])
->where("end_date", ">=", $start_date)
->where("start_date", "<=", $end_date)
->dd();
Level 1
@silencebringer the table name is rates
this is the output of the dump
array:6 [▼
0 => "2021-05-26"
1 => "2021-05-26"
2 => "2021-05-27"
3 => "2021-05-27"
4 => "2021-05-26"
5 => "2021-05-27"
]
how do I interpret this please ?
Level 55
@jroid this is output ob bindings. can you show whole dump (with query)?
if you do not see query (but you should), please do one more:
dd(DB::table("rates")
->selectRaw("sum (datediff(case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value", [$start_date, $start_date, $end_date, $end_date])
->where("end_date", ">=", $start_date)
->where("start_date", "<=", $end_date)
->toSql());
Level 1
@silencebringer can't share the whole outcome here, cos it's not been up to 24hours that I signed up
but the query generated is
"select sum (datediff(case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value from `rates ▶"
Level 55
@jroid please click the arrow at the end of the string and put whole query =)
Level 1
@silencebringer here
"select sum (datediff(case when ? > start_date then ? else start_date end, case when ? < end_date then ? else end_date end) * adult_rate) as my_value from `rates` where `end_date` >= ? and `start_date` <= ? ◀"
Please or to participate in this conversation.