Ruby on Rails sort on priorities

Question

Following were my query to get list of countries along with cities:

@countries_cities = Product.joins(:user).where("country is not null and country <> ''").where("city is not null and city <> ''").where(:users => {:merchant_status => 1}).group(:country, :city).select("country,city").as_json

The output result were as follow:

Object[city:"Bangkok",country:"Thailand"],Object[city:"Phuket",country:"Thailand"],Object[city:"Malaysia",country:"Kuala Lumpur"],Object[city:"Malaysia",country:"Penang"],Object[city:"Shanghai",country:"China"],Object[city:"Beijing",country:"China"]

cchs = @countries_cities.group_by{|cc| cc["country"]}
@search_location_country = cchs

And the view is:

    <ul id="color-dropdown-menu" class="dropdown-menu dropdown-menu-right" role="menu">
      <% @search_location_country.each do |country, cities| %>
        <li class="input" style="background:#ECECEC; "><a href="#" style="font-weight: bold;"><%= country.upcase %></a></li>
                <%  cities.each do |city| %>
        <li class="input"><a href="#"><%= city["city"].titleize %></a></li>
    <% end %>
   <% end %>
</ul>

Now the Drop down result follow this pattern:

Thailand
  -Bangkok
  -Phuket
Malaysia
  -Kuala Lumpur
  -Penang
China
  -Beijing
  -Shanghai

How can I ensure that Malaysia will always place at the top of the drop down lists? Thanks!!

What SQL are you using? MySQL, Postgre? – magni- Apr 13 '16 at 03:10 — magni-, Apr 13 '16 at 03:10

Hieu Pham · Answer 1 · 2016-04-13T02:49:50.410

1

You can customize you query like this:

@countries_cities = Product.joins(:user)
                           .where.not(country: [nil, ''])
                           .where(:users => {:merchant_status => 1})
                           .group(:country, :city)
                           .order("ORDER BY 
                                   CASE WHEN country = 'Malaysia' THEN 1
                                   ELSE 2
                                   END ASC")
                           .select(:country, :city)
                           .as_json

So we set the order of Malaysia = 2, and others = 1 to ensure the result with Malaysia will be on the top.

edited Apr 13 '16 at 02:49

answered Apr 13 '16 at 02:12

Hieu Pham

6,577
2
30
50

Nope, the result seem shows no changing in order of appearance – d3bug3r Apr 13 '16 at 02:24
@d3bug3r: Hmm I just modify my answer, let try again! – Hieu Pham Apr 13 '16 at 02:50
Ok, it shows me some errors now `PG::SyntaxError: ERROR: syntax error at or near "ORDER" LINE 1: ... "products"."country", "products"."city" ORDER BY ORDER BY ` – d3bug3r Apr 13 '16 at 03:10
`: SELECT "products"."country", "products"."city" FROM "products" INNER JOIN "users" ON "users"."id" = "products"."user_id" WHERE (NOT (("products"."country" = '' OR "products"."country" IS NULL))) AND "users"."merchant_status" = $1 GROUP BY "products"."country", "products"."city" ORDER BY ORDER BY CASE WHEN country = 'Malaysia' THEN 1 ELSE 2 END ASC` – d3bug3r Apr 13 '16 at 03:12

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

How about:

@countries_cities = Product.joins(:user)
                           .where.not(country: [nil, ''])
                           .where(users: {merchant_status: 1})
                           .group(:country, :city)
                           .order("country!= 'Malaysia'")
                           .select(:country, :city)
                           .as_json

In Postgres, false is sorted before true (see this answer here: Custom ORDER BY Explanation)

edited May 23 '17 at 11:50

Community

1
1

answered Apr 13 '16 at 03:18

magni-

1,934
17
20

ah, it works now!! how come set `country!= 'Malaysia'` makes `Malaysia` in top? – d3bug3r Apr 13 '16 at 03:22
1

Check out the link for a more detailed explanation, but basically: rows that have `country` as `Malaysia` will have `country!= 'Malaysia'` evaluate to `false`, and those rows get sorted above rows where that evaluates to `true` – magni- Apr 13 '16 at 03:24

Ruby on Rails sort on priorities

2 Answers2