How to retrieve records from database table in Laravel and prevent those which are expired

Question

So I have a table in MySQL which looks like this

these are basically ads that are posted by users. Duration column in this table is about for how many days the User wants to show their ad. that means if user choose to post ad for 3 days i should not just show after 3 days that means it will expire after three days. Now i Want to retrieve only those records which are not expired. I have searched a lot but could not find a way to do this. Kindly give me a solution or refer to a link where i can possibly find the solution.

Right now I am using this script to get the records with simple pagination

$items=Post::with('items','users')->paginate(12);
$categories = DB::table('item_categories')->get();
return view('search_post', compact('items','categories'));

Hint: If the expiration time is the start time plus the duration, then you want all records where the expiration time is in the future. — Alex Howansky, Jan 13 '20 at 21:45
Duration from `created_at` or `updated_at` OR maybe you should also have a `start_date` so you know when to start showing the ad. Lots of unknowns here — RiggsFolly, Jan 13 '20 at 21:56
Or maybe you could have a `start_date` and a `finish_date` that you set when you originally enter the info about the ad and when it should be shown — RiggsFolly, Jan 13 '20 at 21:58
this reference just gave me thought. I might be able to do it easy way by saving the date of expiration and then comparing it to the 'Created_at' column. thank you very much — Hamza Saeed, Jan 13 '20 at 21:58

score 0 · Answer 1 · edited Jan 13 '20 at 21:59

0

You should do an Where Clause with your paginate.

as stated here: https://stackoverflow.com/a/50903963/3808783

like:

$items=Post::with('items','users')
            ->where('created_at', '<=', Carbon::now()->addDays(3))
            ->paginate(12);

edited Jan 13 '20 at 21:59

RiggsFolly

93,638
21
103
149

answered Jan 13 '20 at 21:58

rt3norio

45
6

how do i put that three in addDays() ? – Hamza Saeed Jan 13 '20 at 22:03
you should change your table column name to "expiry" instead of duration. but you've already figured it out. please mark some answer as "Correct" – rt3norio Jan 15 '20 at 01:45

Reza ODB · Answer 2 · 2020-01-13T22:05:36.820

0

Use Carbon to do so. Simply add duration to created_at with Carbon addDay($duration) method. Then return your posts where the result is >= to today().

edited Jan 13 '20 at 22:05

answered Jan 13 '20 at 21:59

Reza ODB

209
2
10

score 0 · Accepted Answer · answered Jan 13 '20 at 22:40

So I changed my table to this

And using this script

$items=Post::with('items','users')->where('duration','>=',\Carbon\Carbon::now() )->paginate(12);
$categories = DB::table('item_categories')->get();
return view('search_post', compact('items','categories'));

It is working perfectly. Thanks to every one.

How to retrieve records from database table in Laravel and prevent those which are expired

3 Answers3