Level 73
You should let the database handle all the filtering and sorting, it is faster and uses less memory than doing it in php.
That being said, can you give an example of the data and the desired result?
Sep 15, 2022
8
Level 3
Query Assistance
I need to form a query but planning it out and still can not seem to think a way to do it.
User has Many Subscription
So i need to find all User(s) who's last active subscription is older than a year. Subscription have. starts_on and expires_on fields to use
Something like orderBy expires_on DESC and then filter thru the collection to return if older than a year?
But probably not user might have a subscription currently not expired so i need to remove that user from the list.
If all of the users subscription are all less than a year remove from the list.
It's more complicated than it seems, or i cant seem to get the query in theory correct.
Any ideas?
Level 3
Essentialy once i get the users that have been expired for 1 year i want to remove all the lingering useless records at this point.
$user has lots of relations so i need to get all the users then go ahead and perform the cleaning i want.
User has Many Subscription so thats the area of confusion
Im using relations so the best thought i had was to get all user.id 's with subscriptions older than 1 year in the first query.
Then use that users.id array / object to query again and return the related subscription which are order by newest expired_on field and if the first subscription is older than 1 year its safe thru my function to remove old files / relations and then the user.
The data is simply i need the user.id's of all users where they have not had an active subscription for over a year.
They can have many subscriptions at the same time so that why the hasMany relationship
Level 102
@movepixels Not sure why you need to retrieve the subscriptions just to delete them?
$userIds = User::where('expired_at', '<', now()->subYear())->pluck('id');
Subscription::whereIn('user_id', $userIds)->delete();
Level 3
@sinnbeck I need to know if the users subscription has expired for more than 1 year 6 moth 5 min....thats not the problem User has Many subscription your code will delete a user if he has a subscription older than 1 year even if he signed up for 1 yesterday. Not as easy as you make it sound
I am only deleting a user if he has a subscription older than 1 year but also if there are no other newer ones. Of all his subscription he can only be deleted if of all have expired more than 1 year
I have a subscription to Netflix from 6 months ago, 1 for Prime 2 years ago, 1 for Huku yesterdy he is fine he has active subscriptions.
Its the user who has no active subscription's of any kind for anything in last year.
Hope that makes more sense and easier to understand. Every subscription that they have if over 1 year then its safe to go ahead and delete, but another might have 5 -> 1 year old subscriptions and one that 360 days expired well he is safe and we dont bother with his user.id because he is still within 1 year
Level 102
@movepixels So do a whereHas query on User?
Level 3
@sinnbeck I still do not think that will work whereHas what? If you include only where >= 1 year your arestill getting users with active profiles. Because its not taking into account we are keeping users with active Subscriptions. User hasMany, if it was hasOne it would work but its having many at any given time in any active or expired state
I think my way of User with Subscriptions and order Subscription where expires_on newest
Then run that collection thru map and if the very first subscription is older than 1 year then thats a user we can say ok this guy is old.
Since all the Subscriptions for each user will be included and ordered expires_on newest expiration so if the first User-> Subscription is over 1 year then any others after guaranteed all older than 1 year hence ensuring that user has zero Subscriptions within 1 year or less.
Thats the only way i think.
Your thoughts?
This is not a commonly run function. Admin side thing run weekly or monthly once so it does not need to be the most 100% professional optimized way.
Level 102
@movepixels Ah should have been whereDoesntHave() probably.
Just to understand it properly. If we break it down to the smallest component. You want a list of users where the amount of active subscriptions = 0?
Level 3
@sinnbeck No, not at all.
Use hasMany Subscriptions. Thats the part you seem to be forgetting, think Netflix, Playboy, Hulu, Prime. Each is a subscription completely separate.
User 1 has Sub 1 => Netfix expired 2 mins ago Sub 2 => Playboy expired 5 years ago Sub 3 => Hulu active Sub 4 => Prime active
Means this user is fine do nothing, DO NOT need user.id
User 2 has Sub 1 => Expired 11 months ago Sub 2 => Playboy expired 5 years ago Sub 3 => Hulu expired 5 years ago Sub 4 => Prime expired 5 years ago
Same do nothing still has 1 thats not over 1 year DO NOT need user.id
User 3 has Sub 1 => Expired 15 months ago Sub 2 => Playboy expired 5 years ago Sub 3 => Hulu expired 5 years ago Sub 4 => Prime expired 5 years ago
All subscription have expired more than 1 year so this user i NEED to get his id.
Use might have 1 subscription or 100. If any of them are under 1 year then we do nothing.
I need every user.id of user where every subscription they have on their account is expired more than 1 year. Any user with any subscription with less than 1 year expiration is exempt and do not need thier id.
Hope that makes a little clearer to understand.
Please or to participate in this conversation.