I'm using the role expiration date in a view. I can see the list of roles that will expire in the future. But the view returns no result (null) when I search for roles that have recently expired e.g. last month.

Am I doing something wrong or is it that it's just not possible? In theory, expiration dates remain available in the uc_roles_expirations table, doesn't matter whether they are past or future.

Use case - having access to the recently expired roles in Views would make it easier to send out custom reminder emails.

Update: Looks like I was wrong to assume that rows for expired roles would remain in the database. This would explain the 'no result' I get when looking for expired roles.

Would be nice to somehow find a way to list the customers/orders whose role has recently expired.

Comments

bisonbleu created an issue. See original summary.

bisonbleu’s picture

OK, I found a way and it seems to work fine for my use case.

- create an Orders view
- add 2 relationships: Order: Customer & Order: Products
- add the fields you require (I used order id, customer first name, last name and email, product title and order date)
- and at least one filter: (customer) User: Ubercart Role expiration date/time (empty)

The trick here is we only want the orders for which there are no role expiration (i.e. expired). So the operator has to be Is empty (NULL).

bisonbleu’s picture

Removing the related issue. 'Referenced by' does the same.

TR’s picture

Thanks for the information. I think this is because Ubercart deletes the row from the {uc_roles_expirations} table when the role expires. This ensures that uc_roles scales well; for example, when searching for newly-expired roles in uc_roles_cron() we don't have to loop through ALL roles ever purchased and compare expiration timestamps to the current timestamp - that quickly becomes impossible over time. But it's fundamentally data destruction; we're loosing historical expiration data that might be really important for customer support, if nothing else. A better data model would have been to have a column in {uc_roles_expirations} which acts as a flag to indicate whether the role was expired or not. This way we could easily query for non-expired roles and THEN compare timestamps, rather than having to loop through the entire table. It would be great to improve this data model for D8, since this is something that was originally designed back in D5 in the pre-Views era where things like this weren't considered.

TR’s picture

Oh, additionally, we have a rules event which sends role expiration reminders - you can customize that message any way you choose, so you don't necessarily need to do this from a View.

TR’s picture

Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.