[SOLVED] CB list and advanced sorting with cbsubs

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

7 years 2 months ago #296788 by krileon

Replied by krileon on topic CB list and advanced sorting with cbsubs

Change status from X to A to sort by expiration date for those that are active. The status values are as follows. In this case you'll probably want to remove the expiration date comparison to NOW().

A = Active
I = Invalid
X = Expired
C = Cancelled
U = Upgraded
R = Renewed

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in or Create an account to join the conversation.

activha
ONLINE
Posts: 2334
Thanks: 117
Karma: 13

7 years 2 months ago #296818 by activha

Replied by activha on topic CB list and advanced sorting with cbsubs

That's not our aim. We'd like to get all the customers who have an expired subscription and no more an active one, we don't want to list the customers with active subscription.

The idea would be to keep :

Code:

( SELECT sub.`expiry_date` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` =  u.`id`  AND `expiry_date` < NOW() AND sub.`status` = 'X' ) DESC

and add something like :

Code:

if last sub.`id` for any sub.`user_id` is not like sub.`status` = 'A'

But I don't know how to write the query to get this in CB List

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

7 years 2 months ago #296822 by krileon

Replied by krileon on topic CB list and advanced sorting with cbsubs

Your query is just an ORDER BY statement. You're sorting the userlist. You can't filter it from there. You need to add an Advanced Filter if you want just expired subscribers as it adds directly to the WHERE statement. See the below for examples.

www.joomlapolis.com/forum/153-professional-member-support/237148-solved-how-to-create-a-list-showing-active-members-only#296273

Please Log in or Create an account to join the conversation.

activha
ONLINE
Posts: 2334
Thanks: 117
Karma: 13

7 years 2 months ago #296823 by activha

Replied by activha on topic CB list and advanced sorting with cbsubs

krileon wrote: You need to add an Advanced Filter if you want just expired subscribers as it adds directly to the WHERE statement. See the below for examples.

www.joomlapolis.com/forum/153-professional-member-support/237148-solved-how-to-create-a-list-showing-active-members-only#296273

Thank you for the example, I had viewed them already.

The thing is that we want all expired subscribers who do not have an active subscription row.
For instance a user can have 5 expired subscriptions and one active subscription. If we use

Code:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 )

we'll get the last expired subscription even if there is a new active one available

How can we restrict the list to the last available row for a user, then check if it's active or not ?

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

7 years 2 months ago #296825 by krileon

Replied by krileon on topic CB list and advanced sorting with cbsubs

That's a count of expired rows. Just do a count of active rows and compare it to 0. The below should mean all users with 0 active subscriptions.

Code:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' ) = 0 )

The following user(s) said Thank You: activha

Please Log in or Create an account to join the conversation.

activha
ONLINE
Posts: 2334
Thanks: 117
Karma: 13

7 years 2 months ago - 7 years 2 months ago #297104 by activha

Replied by activha on topic CB list and advanced sorting with cbsubs

I tried to limite the sorting only to paying plans by adding a IN statement, but I still get all subscriptions.
Do you know how to limit the count to active rows only for chosen paying plans ?

I wrote :

Code:

(  ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` 
AND sub.`status` = 'A' AND sub.`plan_id` IN (6,39,40,41,51,45,31,42,43,44,52)) = 0  )

Thanks
Jean

Last edit: 7 years 2 months ago by activha.

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon