0

The query builder is producing the following query. Everything is correct except for how the parameters are populated:

select
  `citations`.*,
  (
    select
      sum(`settlements`.`amount_dollars`)
    from
      `settlements`
    where
      `citations`.`id` = `settlements`.`billable_id`
      and `settlements`.`billable_type` = ? -- <<< SHOULD BE  'App\Models\Citation'
      and `settlements`.`deleted_at` is null
  ) as `settlements_sum_amount_dollars`
from
  `citations`
  left join (
    SELECT
      `billable_id`,
      sum(`amount_dollars`) AS `amount_paid`
    FROM
      `settlements`
    WHERE
      `billable_type` = 'App\Models\Citation'
      AND `deleted_at` IS NULL
    GROUP BY
      `billable_id`
  ) `payments` on `payments`.`billable_id` = `citations`.`id`
  and `citations`.`amount_dollars` - `citations`.`amount_discount_dollars` > `amount_paid`
  left join people on citations.person_id = people.id
where
  date(citations.created_at) >= 'App\Models\Citation' -- <<< SHOULD BE '2017-10-16'
  and date(citations.created_at) <= '2017-10-16' -- <<< SHOULD BE TODAY
  and `citations`.`deleted_at` is null

The expression that produces this query is effectively the following: (This is not literal code, but an approximate aggregation of the explicit calls that are used to compose the above query.)

Citation::with([
    'person:id,name_first,name_last',
    'employer:id,name',
    'incident',
])
->withSum('settlements', 'amount_dollars')
->leftJoin(
    DB::raw("(SELECT `billable_id`, sum(`amount_dollars`) AS `amount_paid` FROM  `settlements` WHERE `billable_type` = 'App\\Models\\Citation' AND `deleted_at` IS NULL GROUP BY `billable_id`) `payments`"),
    function (JoinClause $join) {
        $join->on('payments.billable_id', '=', 'citations.id')
            ->whereColumn(
                DB::raw("`citations`.`amount_dollars`-`citations`.`amount_discount_dollars`"),
                '>',
                'amount_paid',
            );
    }
)
->where('created_at','>=','2017-10-16')
->where('created_at','<=','2023-10-16') 

What is causing the parameters to map incorrectly, and how do I fix it?

4
  • 1
    I don't see any where in your query that would cause that, or even appends a where on the main query. Can you show your Citation class? Commented Oct 16, 2023 at 18:43
  • Where on earth is the query column created_at in your eloquent? Commented Oct 16, 2023 at 21:42
  • i cant see anything refering to created_at in your query builder. perhaps you have a scope of some sort in the model? Commented Oct 16, 2023 at 22:43
  • Clarified the query builder. Note that the query builder shown here is not literally in the code. Commented Oct 18, 2023 at 16:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.