Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

rose's avatar
Level 1

query no result, but there are results if directly use SQL statement

$query is as follows:

    $query = Policy
        ::join('contracts as c', 'c.id', '=', 'policies.contract_id')
        ->join('market_partners as mp', 'mp.id', '=', 'c.market_partner_id')
        ->join('markets as m', 'm.id', '=', 'mp.market_id')
        ->join('policy_versions as pvall', 'pvall.policy_id', '=', 'policies.id')
        ->where('m.country_code', '=', $market)
        ->where(function ($query) use ($startDateTimeStr, $endDateTimeStr) {
            $query->whereBetween('pvall.created_at', [$startDateTimeStr, $endDateTimeStr])
            ->orWhereBetween('policies.cancelled_at', [$startDateTimeStr, $endDateTimeStr]);
        })
        ->select(['policies.cancelled_at', 'pvall.created_at']);

Mysql query get from $query->toSql() is:

select policies.cancelled_at, pvall.created_at from policies inner join contracts as c on c.id = policies.contract_id inner join market_partners as mp on mp.id = c.market_partner_id inner join markets as m on m.id = mp.market_id inner join policy_versions as pvall on pvall.policy_id = policies.id where m.country_code = ? and (pvall.created_at between ? and ? or policies.cancelled_at between ? and ?)

if I directly replace ? to real Datetime, I can get results.

0 likes
2 replies
Snapey's avatar

Then maybe your $startDateTimeStr and endDateTimeStr are not workable values.

have you checked them before running the query?

What about $market, Is that valid?

rose's avatar
Level 1

@Snapey , I copy paste startDateTimeStr and endDateTimeStr from Log, startDateTimeStr and endDateTimeStr format like this 2017-12-01 00:00:00, $market also valid

Please or to participate in this conversation.