CB Query Field: Country. Need help to make it filter by Country instead of iso

1 month 3 weeks ago #315603 by danpoole
Hi

I have been trying to make my list that contains my field cb_countryinfo display alphabetically by country name but it keeps sorting by iso. I currently have the query set to the one similar to the Documentation : SELECT `iso`, `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` with the options set to Value = iso and Label Column = country. When I try making the Value = country the field would disappear from my front end of the site with no change in how my list is sorted. The list is sorted by the field cb_countryinfo Ascending.

To see the list, here is a link to the front of the site. https://www.emmett-technique-hq.com/practitioners/other-countrieshttps://www.emmett-technique-hq.com/practitioners/other-countries
As can be seen if you view the site it goes Austria then Canada then Switzerland.
Which codes is AT, CA, CH

Could I please get an answer or a nudge in the right direction?
Using Community Builder: 2.4.1 and CB Query Field: 5.0.4

Thanks
1 month 3 weeks ago - 1 month 3 weeks ago #315625 by krileon
The order is entirely determined by your query. So "ORDER BY `country` ASC" or "ORDER BY `country` DESC" should work as you'd expect. This of course only applies to that query select field. Since you're storing ISO and sorting by the stored value in your userlist it's going to sort by ISO. Your only option is to either store `country` or add a subquery to the ORDER BY of your userlist by using Advanced sorting more.

CB Query Field: 5.0.4

Please note the version you are using is over a year old.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
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 EST to 4:00 PM EST. 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.
1 month 3 weeks ago #315634 by danpoole
Thanks Krileon

I have tried changing the query to SELECT `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` making the Value = country as well as the label = country, it removes the country field from the front end and ordering doesn't change. I have attached 2 images showing the difference. Otherwise how would I be able to do the subquery as I attempted before with no success

Attachments:
1 month 2 weeks ago #315643 by Astrid
Advanced:
ue.`cb_countryinfo` ASC
1 month 2 weeks ago #315654 by krileon

I have tried changing the query to SELECT `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` making the Value = country as well as the label = country, it removes the country field from the front end and ordering doesn't change. I have attached 2 images showing the difference. Otherwise how would I be able to do the subquery as I attempted before with no success

You can't change the value the field stores and expect it to dynamically update every user row to the new values. That's just not how it works. For your change to take affect you need to re-save all those profiles with country re-selected. Your alternative is to use a subquery in the ORDER BY of the userlist query, which is done by setting the ordering to Advanced to supplying the necessary SQL.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
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 EST to 4:00 PM EST. 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.
1 week 5 days ago - 1 week 5 days ago #316210 by danpoole

For your change to take affect you need to re-save all those profiles with country re-selected. Your alternative is to use a subquery in the ORDER BY of the userlist query, which is done by setting the ordering to Advanced to supplying the necessary SQL.


As I have such a big database, re-saving all the profiles is not feasible. I have tried doing SQL in the Advanced normally and with the information provided by your help documents but with no success. I don't seem to understand the correct formatting in which to do the sub query in the ORDER BY query for my list. Would you please be able to provide an example on how to do the sub query.
Moderators: beatnantkrileon
Time to create page: 0.549 seconds

Facebook Twitter LinkedIn