Sort List

Is it possible to order list results according to #__cbsubs_plans table e.g. SELECT `name` WHERE `name`=Basic, `name`=SuperBasic, etc

As far as I understand List->Sorting->Advanced doesn't accept a full SQL statement like SELECT `name` FROM #__cbsubs_plans etc., is that correct?

Should it be somehow directly stated as ue.`name` or something?

Also, I don't see the EMPTY, NOT EMPTY operator for the fields.

Yes, that's possible but it won't be very efficient performance wise. So depending on how large your userbase gets it could end up being quite slow. I assume you want to sort the userlist based off users subscription to your plans? The below should do this fine.


This should for example sort them based off their subscription date if they have an active subscription it will then sort by their name. If you want to filter the userlist so it only shows users subscribed to a specific plan then the below should also work.

Active
Expired
This isn't filtered to a specific plan. If you only want to show from a specific plan you'd use the below for example.

Active
Expired
Be sure to replace PLAN_ID_HERE with your actual plan id.

I will try exactly as you described.

Actually what I am trying to achieve are 2 things.

1. to have a list showing the users with a specific plan order e.g. first users ASC by Plan1, then users ASC by Plan2, etc
2. in order to narrow down the results, in Search options I need to make all fields required to have a value

1. to have a list showing the users with a specific plan order e.g. first users ASC by Plan1, then users ASC by Plan2, etc

You should be able to do that by adjusting the query provided above. Example as follows.

( SELECT sub.`plan_id` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' ) DESC, u.`name` ASC

That should sort by plan id instead of by subscription date. You could sort by plan name as well I suppose. Basically just need to adjust that subquery as needed.

( SELECT sub.`plan_id` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' ) DESC, u.`name` ASC

1242
Subquery returns more than 1 row

EDIT: disregard
EDIT2: I thought I fixed it but no

They've multiple active subscriptions then. You'll need to further limit the query to prevent that. Either using GROUP BY and concating the results or adding LIMIT 1 to the query or by adding additional WHERE statements to limit what it queries for.