Sort List

galanopd
OFFLINE
Posts: 374
Thanks: 49
Karma: 8

3 years 1 month ago - 3 years 4 weeks ago #326832 by galanopd

Replied by galanopd on topic Sort List

I think I made the query work by grouping the parent plants.

EDIT: It doesn't produce the order I need. Maybe I am missing something in the ORDER BY FIELD (sub.`plan_id`,....) ?

Code:

( SELECT sub.`plan_id` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' AND sub.`parent_plan` in (1,4,10,11,14,18) GROUP BY sub.`parent_plan` ORDER BY FIELD (sub.`plan_id`,14,35,34,33,32,31,17,16,15,4,29,28,27,13,26,25,12,9,10,8,24,23,7,5,18,30,21,20,1,19,2)>= 1 )

Last edit: 3 years 4 weeks ago by galanopd.

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

galanopd
OFFLINE
Posts: 374
Thanks: 49
Karma: 8

3 years 3 weeks ago - 3 years 3 weeks ago #326861 by galanopd

Replied by galanopd on topic Sort List

It seems that in fiddle I get the expected result, to order by parent_plan and then by plan_id in the order I need while in List->Filters->Advanced I can't.
I can't understand why...

Here is the query in List

Code:

( SELECT sub.`parent_plan`
FROM `#__cbsubs_subscriptions` AS sub
WHERE sub.`user_id` = u.`id`
AND sub.`status` = 'A'
AND sub.`parent_plan` in (14, 11, 10, 18, 1)
ORDER BY FIELD (sub.`plan_id`,14,35,34,33,32,31,17,16,15,11, 29,28,27,13,26,25,12,9,10, 8,24,23,7,5,18, 30,21,20,3,1,19,2) )

Last edit: 3 years 3 weeks ago by galanopd.

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

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

3 years 3 weeks ago #326862 by krileon

Replied by krileon on topic Sort List

The Advanced Sorting just directly adds whatever your query is to the userlist ORDER BY clause. So you're ending up with the following.

Code:

ORDER BY ( SELECT sub.`parent_plan`
FROM `#__cbsubs_subscriptions` AS sub
WHERE sub.`user_id` = u.`id`
AND sub.`status` = 'A'
AND sub.`parent_plan` in (14, 11, 10, 18, 1)
ORDER BY FIELD (sub.`plan_id`,14,35,34,33,32,31,17,16,15,11, 29,28,27,13,26,25,12,9,10, 8,24,23,7,5,18, 30,21,20,3,1,19,2) )

You need to remove the >= part as noted in my reply that's for filtering the userlist not sorting it. My sorting example was as follows.

Code:

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

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.

galanopd
OFFLINE
Posts: 374
Thanks: 49
Karma: 8

3 years 3 weeks ago - 3 years 3 weeks ago #326863 by galanopd

Replied by galanopd on topic Sort List

What I need to have is a list

1.Group by parent plans in the order I need (14, 11, 10, 18, 1)
2.Order by plan id under each parent plan in the order I need (14,35,34,33,32,31,17,16,15,11, 29,28,27,13,26,25,12,9,10, 8,24,23,7,5,18, 30,21,20,3,1,19,2)
3.Order by region
4.Order by sub-region
5.Order by country
6.Order by state
7.Order by city

So, do I have to use query for filtering and query for sorting or just one query for all?
Moreover, when I put my query in the Sorting, the outcome is different from fiddle. It is like ORDER BY FIELD doesn't exist for the list!
Your example doesn't help me to understand the proccess, maybe my fault. I kindly ask you to elaborate a bit more if possible.

Last edit: 3 years 3 weeks ago by galanopd.

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

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

3 years 3 weeks ago #326864 by krileon

Replied by krileon on topic Sort List

You likely aren't going to be able to sort it so elaborately. The Advanced Sorting just directly adds whatever you supply to the ORDER BY clause of the userlist query. There isn't much more detail I can go in without writing the entire query for you which we don't do here. We can only provide simple examples which I have in previous replies. If you want to change the order you need to use the Sorting tab not the Filters tab. My above example sorts by subscription date followed by their name.

Another example as follows that sorts by plan id followed by name.

Code:

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

The following user(s) said Thank You: galanopd

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

galanopd
OFFLINE
Posts: 374
Thanks: 49
Karma: 8

3 years 3 weeks ago #326872 by galanopd

Replied by galanopd on topic Sort List

It is ok Kyle,
I understand.

Your information above was helpful and I am starting to understand how it works.

The following user(s) said Thank You: krileon

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

Moderators: beat, nant, krileon