Sort List
- galanopd
- OFFLINE
-
Platinum Member
- Posts: 327
- Thanks: 42
- Karma: 8
2 years 5 months ago - 2 years 5 months 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`,....) ?
EDIT: It doesn't produce the order I need. Maybe I am missing something in the ORDER BY FIELD (sub.`plan_id`,....) ?
( 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: 2 years 5 months ago by galanopd.
Please Log in to join the conversation.
- galanopd
- OFFLINE
-
Platinum Member
- Posts: 327
- Thanks: 42
- Karma: 8
2 years 5 months ago - 2 years 5 months 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
I can't understand why...
Here is the query in List
( 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: 2 years 5 months ago by galanopd.
Please Log in to join the conversation.
krileon
Team Member- OFFLINE
- Posts: 68492
- Thanks: 9080
- Karma: 1434
2 years 5 months ago #326862
by krileon
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.
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.
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.
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.
( SELECT sub.`subscription_date` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' ) DESC, u.`name` ASCKyle (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 to join the conversation.
- galanopd
- OFFLINE
-
Platinum Member
- Posts: 327
- Thanks: 42
- Karma: 8
2 years 5 months ago - 2 years 5 months 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.
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: 2 years 5 months ago by galanopd.
Please Log in to join the conversation.
- krileon
- Team Member
- OFFLINE
- Posts: 68492
- Thanks: 9080
- Karma: 1434
2 years 5 months ago #326864
by krileon
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.
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.
Another example as follows that sorts by plan id followed by name.
( SELECT sub.`plan_id` FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'A' ) DESC, u.`name` ASCKyle (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.
The following user(s) said Thank You: galanopd
Please Log in to join the conversation.
- galanopd
- OFFLINE
-
Platinum Member
- Posts: 327
- Thanks: 42
- Karma: 8
Moderators: beat, nant, krileon
Time to create page: 0.228 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Developer Members Support
- Sort List