Laravel Query Builder Join method date where date is not working properly.

I have this list of property_prices that belongs to a property


property_prices table

|id  |property_id|price|date      |
|10  |6           |100  |2017-06-22 | 
|11  |6           |200  |2017-06-23| 
|12  |6           |300  |2017-06-24| 
|13  |6           |400  |2017-06-25|

My code looks like this

$query
    ->select('properties.id')
    ->join('property_prices', function ($join) {
            $join->on('properties.id', '=', 'property_prices.property_id')
            ->where('property_prices.date', '<=', '2017-06-24');
        })
        ->addSelect('property_prices.id as price_id')
    ->addSelect('property_prices.date as price_date')
        ->addSelect('property_prices.price as price');
    

    dd($query->first());

    // The result is
    |id   |property_id|price|date      |
    |10  |6            |100  |2017-06-22 | 

    // now when I change ->where('property_prices.date', '<=', '2017-06-24')
    // to ->where('property_prices.date', '=', '2017-06-24')

    // I get the expected result which is 
    |id   |property_id|price|date     |
    |12  |6            |300  |2017-06-24|

AddWebContribution

8 years ago

Best Answer

Level 42

If I understand your request correctly, you want below result when you write dd($query->first()); with ->where('property_prices.date', '<=', '2017-06-24')

|id   |property_id|price|date     |
    |12  |6            |300  |2017-06-24|

By default order is ascending so complete result is:


|id  |property_id|price|date      |
|10  |6           |100  |2017-06-22 | 
|11  |6           |200  |2017-06-23| 
|12  |6           |300  |2017-06-24|

Now are filtering this records by dd($query->first()); so this line will return you above result. But you can change the order of result using:

->orderBy('property_prices.date', 'desc');

Try above orderBy() and let me know if this will not return you your expected result.

1 like

jericopulvera

8 years ago

Level 7

@saurabh orderBy did nothing it still always returns the lowest date which is

|id  |property_id|price|date      |
|10  |6           |100  |2017-06-22 |

jlrdw

8 years ago

Level 75

I believe you are missing the

->get();

Please or to participate in this conversation.