1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Sorting user list by 2 values

Sorting user list by 2 values

6 years 4 months ago #300031 by softforge
Sorting user list by 2 values was created by softforge
I am trying to make a members list which sorts by all the members in an area and then places the main contact in that area at the top of each of the areas
The areas are stored in a table and cb uses query drop down to show them to the users.Then there is the question are you the main contact for this area.
I can get the users group into the areas using
'xxxx_user_area.ordering' ASC

This gives me

fred Blogs Avon No
Harry Blogs Avon No
Jeff Smith Avon Yes
Harry Smith Avon No
Sam Blogs Devon No
Harry Cheese Devon Yes
John Smith Devon No
Sam Warren Devon No

I want

Jeff Smith Avon Yes
fred Blogs Avon No
Harry Blogs Avon No
Harry Smith Avon No
Harry Cheese Devon Yes
Sam Blogs Devon No
John Smith Devon No
Sam Warren Devon No

So the main contact is always at the top of the area group

I tried
ue.`cb_maincontact` , 'xxxx_user_area.ordering' ASC
and
'xxxx_user_area.ordering' , ASC, ue.`cb_maincontact`

But I get this


fred Blogs Avon No
Harry Blogs Avon No
Harry Smith Avon No
Sam Blogs Devon No
John Smith Devon No
Sam Warren Devon No
Jeff Smith Avon Yes
Harry Cheese Devon Yes

and this


Jeff Smith Avon Yes
Harry Cheese Devon Yes
fred Blogs Avon No
Harry Blogs Avon No
Harry Smith Avon No
Sam Blogs Devon No
John Smith Devon No
Sam Warren Devon No


Any idea what I am doing wrong?
If you like our plugins, please consider writing a review on the Joomla Extension Directory or the Community Builder Directory ...

Please Log in to join the conversation.

6 years 4 months ago #300070 by krileon
Replied by krileon on topic Sorting user list by 2 values
If you need the ordering column of the users selected value from another table you need to do an actual subquery. Example as follows.

( SELECT area.`ordering` FROM `#__user_area` AS area WHERE area.`value` = ue.`cb_userarea`  ) ASC, ue.`cb_maincontact` 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 to join the conversation.

6 years 3 months ago - 6 years 3 months ago #300741 by softforge
Replied by softforge on topic Sorting user list by 2 values
This message contains confidential information
If you like our plugins, please consider writing a review on the Joomla Extension Directory or the Community Builder Directory ...

Please Log in to join the conversation.

6 years 3 months ago #300750 by krileon
Replied by krileon on topic Sorting user list by 2 values
See my above reply where I've already explained how to sort by an external table. You need to set Oder By to Advanced and add your custom SQL ORDER BY clause there, which needs to be a subquery. You can do the same for filtering, which directly adds to the WHERE statement of the userlist query. There is no functionality to add joins to a userlist query unless you do so by acting on the appropriate userlist triggers and extend it using custom coding. See the below MYSQL documentation regarding order by if unsure how order by sorts in SQL.

dev.mysql.com/doc/refman/5.7/en/sorting-rows.html
dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Please in the future do not include your entire post in a confidential tag. Make public what is possible and do not include login credentials (which I have removed) even in confidential tags.

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 to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Sorting user list by 2 values
Time to create page: 0.533 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. Sorting user list by 2 values

Search

Login / Logout

User Menu