How should I convert the below SQL query to Laravel's Eloquent query builder?
select count(x.payment)
from (
select *
from transactions
group by payment
having count(payment) = 1
) x
where x.updated_at between "2021-06-14" AND "2022-06-20"
There is not really a reliable tool to convert automatically. In my opinion, the easiest way is to decompose your query to convert it by following the official Laravel documentation.
Here is an example using the DB facade, you can adapt using your models directly. You can start by converting the subquery :
DB::query()->fromSub(function ($query) {
$query->from('transactions')
->groupBy('payment')
->havingRaw('COUNT(payment) = ?', [1])
}, 'a');
Then you have a between condition where x.updated_at between "2021-06-14" AND "2022-06-20":
$from = date('2018-01-01');
$to = date('2022-06-20')
DB::query()->fromSub(function ($query) {
$query->from('transactions')
->groupBy('payment')
->havingRaw('COUNT(payment) = ?', [1])
}, 'a')->whereBetween('updated_at', [$from, $to]);
Finally, you want to count the total number of results. To do this, you can use count():
$from = date('2018-01-01');
$to = date('2022-06-20')
DB::query()->fromSub(function ($query) {
$query->from('transactions')
->groupBy('payment')
->havingRaw('COUNT(payment) = ?', [1])
}, 'a')->whereBetween('updated_at', [$from, $to])->count();
