How to Do Case-Insensitive Order in Rails with Postgresql

How to do case-insensitive order in Rails with postgresql

result = Users.find(:all, :order => "LOWER(name)")

To take a little bit from both Brad and Frank.

Postgres case insensitive searching with Rails

Case insensitive searching in Postgres:

• use ilike instead of like (case-insensitive like)
• if you want to use =, make both sides either UPPER or LOWER

How to write a case insensitive query and only return unique records in Rails

You can do this with Postgres Window functions. You'll want to look into the row_number() function.

Here is how you can achieve your desired result:

select id,fro,tos
FROM
(
select id,fro,
tos, row_number() over (partition by LOWER(tos)  ORDER BY LOWER(tos) DESC) AS alpharow
from stacks
) flattened
where alpharow = 1

I had to use alternate names for "to" and "from" as those are reserved words - but it should be obvious what they map too in your example.

Some followup readings:

https://buildingvts.com/understanding-postgres-window-functions-697bc0ff2ed4

https://robots.thoughtbot.com/postgres-window-functions

Case insensitive search using key value in rails and postgresql

try using a combination of LOWER (sql) and downcase (ruby)

  search = Model.using_scope
  search = search.where('LOWER(first_name) = ?', params[:first_name].downcase) unless params[:first_name].blank?
  search = search.where('LOWER(last_name) = ?', params[:last_name].downcase) unless params[:last_name].blank?
  search = search.where('specialities.name = ?', params[:speciality]) unless params[:speciality].blank?
  search = search.where('locations.zipcode = ?', params[:zipcode]) unless params[:zipcode].blank?

How to order associated columns case insensitive

You need to write it using references.

Project.includes(:customer)
       .order("LOWER(customers.company) ASC")
       .references(:customers)

Case insensitive ordering for citext field type in rails

Maybe you could try with:

@offices.order('LOWER(organisations.name)')

Case-insensitive match in ActiveRecord with LOWER() not finding any matches

Doing this in Postgres gives the reason. This is an encoding issue, you're using Cyrillic Letter A and Rails/Postgres is probably trying to compare it to a Basic Latin A, giving you unexpected results.

SELECT 
    ASCII(LOWER('Апелласьон')),  -- 1072
    ASCII(LOWER('апелласьон')),  -- 1072
    ASCII(LOWER('a')),           -- 97
    ASCII(LOWER('A'))            -- 97

---

Here are the ASCII codes for each character

https://www.codetable.net/decimal/1072

https://www.codetable.net/decimal/97

---

Someone with more knowledge of character codes in Postgres will have to give a more meaningful explanation.

What's the best way to make a db column case insensitive in Rails?

A db column is never case insensitive. Your interaction may be case insensitive. As you saw, there are several possible cases and solutions.

If the attribute has sense only in a case, but you want to allow searching regardless the case, then always normalize it into a case when writing to the database and filter the queries that access to it. A typical example is an email address. You want to store it always lowercase and make sure to query it in such way, lowering the case of user inputs.

Vice-versa, if in the field you want to save the case is important (such as Title or brand) but you want to allow case insensitive interaction, you have basically two options

1. Add a separate field that contains the attribute always case insensitive and proceed as in the previous example
2. Use the database engine functions when you query the database in order to convert the value at runtime. Keep in mind that this will probably prevent the database from being able to use indexes, thus it may result in degraded performances.

Case-insensitive search in Rails model

You'll probably have to be more verbose here

name = "Blue Jeans"
model = Product.where('lower(name) = ?', name.downcase).first 
model ||= Product.create(:name => name)

How to make case-insensitive query in Postgresql?

Use LOWER function to convert the strings to lower case before comparing.

Try this:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

---

Related Topics