How to get last entry for model in given date range from pivot table

Grab only the last created item based on the conditions

$projects = Item::with('statuses')
    ->whereBetween('created_at', array(Carbon::now(), Carbon::now()->addWeek()))
    ->whereIn('status_id', [7, 9])
        ->orderBy('created_at', 'desc')
    ->first();

Grab a list of items based on the conditions ordered by latest added

$projects = Item::with('statuses')
    ->whereBetween('created_at', array(Carbon::now(), Carbon::now()->addWeek()))
    ->whereIn('status_id', [7, 9])
        ->orderBy('created_at', 'desc')
    ->get();

sorry... 'recur_at' ?

the "created_at" .. :-) I think he mixed his mother tongue with english :-D

Updated! My bad!

i see... but something doesn't seems ok... i don't want items which are created between dates, but pull only item ids from pivot table which have last (most recent) status in given period. for ex.

item_status
item_id status_id   created_at
1       1       2015-10-06
1       3       2015-10-07
2       6       2015-10-07
2       3       2015-10-08
2       5       2015-10-09

if i set filter as: status_id = 3 and date range 2015-10-07 to 2015-10-09: i want only ITEM 1, because ITEM 2 in given period has another, newer status (5)

it's kinda hard to explain... but hope you get it. tnx

If i get you correct, @bobbybouwmann first code snippet is the one you want.

no, i get "Column not found: 1054 Unknown column 'status_id' in 'where clause' ... i don't have 'status_id' column in items table...

You can make your own query right? I don't know your database structure... I just copied your example and I see know that you have a whereHas so in that case you need something like this

$projects = Item::with('statuses')->whereHas('statuses', function ($query) {
    $query->whereBetween('created_at', array(Carbon::now(), Carbon::now()->addWeek()))
    $query->whereIn('status_id', [7, 9])
})->orderBy('created_at', 'desc')->first();

Please for the next time! Think for yourself. You could easily just remove the column and continue right?

if i could make it on my own, i wouldn't ask here. i stated table strucures at begining of question. Thanks for your effort, but i don't think you understand problem here. Your code is pretty much same as mine from 1st post. look at example table i put 4 posts before... if you use

$items = Item::with('statuses')->whereHas('statuses', function ($q) {
                                        $q->where('created_at','>=','2015-10-06');
                                        $q->where('created_at','<','2015-10-09');
                                        $q->whereIn('status_id',array('3'));
                                    }
                    );

i will get ITEM 1 and ITEM 2, but i need only ITEM 1 because in given period ITEM 2 has another, newer status (5)

i tried to add another relation method to item model:

public function lateststatus(){
    return $this->belongsToMany('Repairstatus')
        ->orderBy('item_status.created_at','desc')
        ->withTimestamps()
        ->limit(1,0);
}

and then tried to use that method, but result was same

$items = Item::with('lateststatus')->whereHas('lateststatus', function ($q) {
                                        $q->where('created_at','>=','2015-10-06');
                                        $q->where('created_at','<','2015-10-09');
                                        $q->whereIn('status_id',array('3'));
                                    }
                    );

Here's SQL that did thing. Is there way to make it through Eloquent? INNER JOIN part is for "ordering" before GROUP BY, - i needed results grouped by item_id, but most recent entries.

SELECT t1.item_id FROM item_status t1
    INNER JOIN (
                    SELECT MAX(created_at) created_at, item_id
                    FROM item_status 
                GROUP BY item_id
                     ) t2
    ON t1.item_id = t2.item_id 
    AND t1.created_at = t2.created_at 

WHERE t1.status_id in (3), 
    AND t1.created_at >= '2015-10-06' 
    AND t1.created_at < '2015-10-09'

You need to use a join then: http://laravel.com/docs/5.1/queries#joins