Sort List

Can you please have a look at the query below and tell me why it throws an error while on fiddle www.db-fiddle.com/f/gi6vnWVYQddvXZt1vpxw2L/5 seem to work?

You're just running an independent query is why it works fine because as a query there's nothing wrong with it, but as a subquery in an ORDER BY clause it won't work. The Advanced Sorting directly adds whatever you supply to the ORDER BY clause. Example as follows.

Advanced Sorting: Result: Userlist Query:

After quite a lot of "pain" I need to ask, is it doable within the sorting tab to follow this specific order within the subquery or am I simply wasting my time?

I have tried almost everything following your examples but no luck so far.

Thanks

EDIT: Ok I think I have ended up with something that seems to be working. I would appreciate it to have a look and tell me if I am on the right path.

I don't think that's correct as you're again treating it like a standard query. You're adding a subquery to the ORDER BY clause so it's not the same as just running a regular query. I think something like the below might work purely based off your own code, but I did not test it.


Might not work if they've more than 1 subscription though so might need to add LIMIT 1 to the subscription query.


This is probably going to be pretty bad performance though. It'd actually be better to create a field in CB > Field Management to hold their plan index value. I recommend using an integer field. You can then hide it from profile edit using CB Conditional or CB Privacy. Next within your plans using the CBSubs Fields integration set whatever order index you want for each plan in those plans field settings. Now you'd just order your userlist by that field and not need a subquery at all, but you'll need to update the database for all existing subscribers. So for example you'd have the following within your plans.

Plan 14
Integrations > Fields
Field: cb_planindex
Operator: Set
Value: 1
Remove value on plan deactivation: Yes

Plan 35
Integrations > Fields
Field: cb_planindex
Operator: Set
Value: 2
Remove value on plan deactivation: Yes

No, neither mine nor yours work perfectly. They fail to sort correctly according to all ORDER BY commands.
So I will follow your advice and create a field in CB > Field Management.

I've waisted too much time already with this. Your suggestion seems to be far better.

Thank you Kyle

I was thinking...
If a user subscribes to a plan, he will also be subscribed to its parent plan.
Now, since the planindex field is used for one plan, which of the two will it append?