spatie/ray

Ray does not output 0 and 1 as query bindings

Closed this issue · 5 comments

mbaric commented

Describe the bug

For the eloquent code

 public function checkAllSelectInvestmentDeals(): array
    {
        $dealInvestments = Deal_investment::selectRaw('deal.deal_id,loan.loan_id,loan.loan_name,loan.loan_end_date')
        ->join('deal', 'deal_investment.deal_id', '=', 'deal.deal_id')
        ->join('loan', 'deal.loan_id', '=', 'loan.loan_id')
        ->whereRaw("loan.loan_end_date <= DATE_FORMAT(CURDATE(), '%Y-%m-01')")
        ->whereRaw('deal_investment.user_id!=2912')
        ->where([
            'deal.flag_exclude'                   => 0,
            'deal_investment.flag_capital_repaid' => 0,
            'deal_investment.flag_pool'           => 0,
            'deal.flag_is_active'                 => 1,
            'deal.flag_deleted'                   => 0
        ])
            ->groupBy('deal.deal_id')
            ->havingRaw("COUNT(*) > 0")
            ->selectRaw('count(*) as number_of_investment')
            ->get()->toArray();
        
        return $dealInvestments;
    }

Ray outputs this:

image

select
  deal.deal_id,
  loan.loan_id,
  loan.loan_name,
  loan.loan_end_date,
  count(*) as number_of_investment
from
  `deal_investment`
  inner join `deal` on `deal_investment`.`deal_id` = `deal`.`deal_id`
  inner join `loan` on `deal`.`loan_id` = `loan`.`loan_id`
where
  loan.loan_end_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
  and deal_investment.user_id != 2912
  and (
    `deal`.`flag_exclude` =
    and `deal_investment`.`flag_capital_repaid` =
    and `deal_investment`.`flag_pool` =
    and `deal`.`flag_is_active` =
    and `deal`.`flag_deleted` =
  )
group by
  `deal`.`deal_id`
having
  COUNT(*) > 0

The nested Where clause and it's values are not inclduded

Versions

  • Ray: 2.6.7
  • spatie/ray 1.37.2
  • spatie/laravel-ray 1.32.4
  • PHP version: 8.1.11
  • Laravel version: 9.52.9

To Reproduce
Original code written above with the output

Expected behavior

select
  deal.deal_id,
  loan.loan_id,
  loan.loan_name,
  loan.loan_end_date,
  count(*) as number_of_investment
from
  `deal_investment`
  inner join `deal` on `deal_investment`.`deal_id` = `deal`.`deal_id`
  inner join `loan` on `deal`.`loan_id` = `loan`.`loan_id`
where
  loan.loan_end_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
  and deal_investment.user_id != 2912
  and (
    `deal`.`flag_exclude` = 0
    and `deal_investment`.`flag_capital_repaid` = 0
    and `deal_investment`.`flag_pool` = 0
    and `deal`.`flag_is_active` = 1
    and `deal`.`flag_deleted` = 0
  )
group by
  `deal`.`deal_id`
having
  COUNT(*) > 0

Desktop:
Ubuntu 22.04.3 LTS

mbaric commented

I also tried writing it like this:

public static function checkAllSelectInvestmentDeals(): array
    {
        $dealInvestments = Deal_investment::selectRaw('deal.deal_id,loan.loan_id,loan.loan_name,loan.loan_end_date')
            ->join('deal', 'deal_investment.deal_id', '=', 'deal.deal_id')
            ->join('loan', 'deal.loan_id', '=', 'loan.loan_id')
            ->whereRaw("loan.loan_end_date <= DATE_FORMAT(CURDATE(), '%Y-%m-01')")
            ->whereRaw('deal_investment.user_id!=2912')
            ->where([
                ['deal.flag_exclude', 0],
                ['deal_investment.flag_capital_repaid', 0],
                ['deal_investment.flag_pool', 0],
                ['deal.flag_is_active', 1],
                ['deal.flag_deleted', 0],
            ])
            ->groupBy('deal.deal_id')
            ->havingRaw("COUNT(*) > 0")
            ->selectRaw('count(*) as number_of_investment')
            ->get()->toArray();
        
        return $dealInvestments;
    }

I still got the same in Ray:

image

select
  deal.deal_id,
  loan.loan_id,
  loan.loan_name,
  loan.loan_end_date,
  count(*) as number_of_investment
from
  `deal_investment`
  inner join `deal` on `deal_investment`.`deal_id` = `deal`.`deal_id`
  inner join `loan` on `deal`.`loan_id` = `loan`.`loan_id`
where
  loan.loan_end_date <= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  and deal_investment.user_id != 2912
  and (
    `deal`.`flag_exclude` =
    and `deal_investment`.`flag_capital_repaid` =
    and `deal_investment`.`flag_pool` =
    and `deal`.`flag_is_active` =
    and `deal`.`flag_deleted` =
  )
group by
  `deal`.`deal_id`
having
  COUNT(*) > 0
pczartoryski commented

+1 , same here. It worked fine recently.

Sam-Apostel commented

I couldn't replicate with my tests until I tried using 0 and 1 as bindings, which both fail.

Thanks for providing detailed instructions!

  • 👍1
Sam-Apostel commented

fixed in v2.6.9

mbaric commented

Hi @Sam-Apostel . Thanks for taking care of this. I confirm in v2.6.9 it's working.

image

  • 👍1