Laravel-4 query builder not returning expected results

Question

I'm a bit perplexed by this one. I have built a query in laravel, that is being interpreted as I would expect, but it's returning an empty result set when it shouldn't be.

This is my laravel code:

$results = Outstanding::where('Outstanding.creditor_id', '=', $creditor->id)
    ->whereBetween('Outstanding.yyyymm', array($this->last_yyyymm, $this->now))
    ->select('Outstanding.yyyymm',
        'Outstanding.deviation_30',
        'Outstanding.deviation_60',
        'Outstanding.deviation_90',
        'Outstanding.deviation_over_90',
        'Outstanding.pay_amt',
        'Outstanding.outstanding',
        'Monthly_Historical.inv_on_hold_sum')
    ->leftJoin('Monthly_Historical', function($join) use($creditor){
        $join->on('Outstanding.yyyymm', '=', 'Monthly_Historical.yyyymm')
        ->where('Monthly_Historical.creditor_id', '=', $creditor->id);
    })
    ->groupBy('Outstanding.yyyymm')
        ->get();

When I look at the query that this is being turned into, I see the following:

select `Outstanding`.`yyyymm`, `Outstanding`.`deviation_30`, 
`Outstanding`.`deviation_60`, `Outstanding`.`deviation_90`, 
`Outstanding`.`deviation_over_90`, `Outstanding`.`pay_amt`, 
`Outstanding`.`outstanding`, `Monthly_Historical`.`inv_on_hold_sum` 
from `Outstanding` left join `Monthly_Historical` on 
`Outstanding`.`yyyymm` = `Monthly_Historical`.`yyyymm` and 
`Monthly_Historical`.`creditor_id` = ? where `Outstanding`.`creditor_id` = ? and 
`Outstanding`.`yyyymm` between ? and ? group by `Outstanding`.`yyyymm`
array(3) {
  [0]=>
  int(2)
  [1]=>
  string(6) "201301"
  [2]=>
  string(6) "201401"

When I copy/paste this into MySql workbench, and replace the ?s with their respective values, I get exactly the result set I expect. At first I thought the dates shouldn't be strings, but in the workbench I get the correct result set regardless of passing them as strings or ints. The only other thought I have is that the array of parameters for the query should have 4 values in it, one for each ? in the query, but I can't control that (at least as far as I'm aware.)

Can anyone see something I am missing? Or is this a known bug in Laravel 4?

Thanks in advance for your help.

-Eric

Mark Baker · Accepted Answer · 2014-03-17 16:54:51Z

The problem is the order of the bindings: always do JOINs before WHERE clauses

$results = Outstanding::select('Outstanding.yyyymm',
        'Outstanding.deviation_30',
        'Outstanding.deviation_60',
        'Outstanding.deviation_90',
        'Outstanding.deviation_over_90',
        'Outstanding.pay_amt',
        'Outstanding.outstanding',
        'Monthly_Historical.inv_on_hold_sum')
    ->leftJoin('Monthly_Historical', function($join) use($creditor){
        $join->on('Outstanding.yyyymm', '=', 'Monthly_Historical.yyyymm')
        ->where('Monthly_Historical.creditor_id', '=', $creditor->id);
    })
    ->where('Outstanding.creditor_id', '=', $creditor->id)
    ->whereBetween('Outstanding.yyyymm', array($this->last_yyyymm, $this->now))
    ->groupBy('Outstanding.yyyymm')
        ->get();

Manually setting bindings for a join may also help force the ordering of the bindings

$results = Outstanding::select(
    'Outstanding.yyyymm',
    'Outstanding.deviation_30',
    'Outstanding.deviation_60',
    'Outstanding.deviation_90',
    'Outstanding.deviation_over_90',
    'Outstanding.pay_amt',
    'Outstanding.outstanding',
    'Monthly_Historical.inv_on_hold_sum'
)->leftJoin(
    'Monthly_Historical', 
    function($join) use($creditor) {
        $join->on('Outstanding.yyyymm', '=', 'Monthly_Historical.yyyymm')
        ->on('Monthly_Historical.creditor_id', '=', DB::raw('?'));
    })
    ->setBindings(
        array_merge(
            $this->query->getBindings(),
            array($creditor->id)
        )
    )
    ->where('Outstanding.creditor_id', '=', $creditor->id)
    ->whereBetween('Outstanding.yyyymm', array($this->last_yyyymm, $this->now))
    ->groupBy('Outstanding.yyyymm')
        ->get();

which will ensure you get your missing binding value

Thanks! This worked. I can't accept it for ~7 minutes, but I will when that time comes up.

Collectives™ on Stack Overflow

Laravel-4 query builder not returning expected results

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related