CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-activerecord
lambdap
lambdap

Reputation: 3

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

I am using Rails 4 and PostgreSQL and trying to figure out a way to write a case insensitive query method that returns records such that you get only one of the case insensitive duplicates. For a simple example, suppose you have an Email model that represents sent email, and has id, to, from, and your data looks like:

id| to              | from
------------------------------------
1 | [email protected] | [email protected]
2 | [email protected] | [email protected]
3 | [email protected]    | [email protected]

and I want the query method to only return:

[<#Email:0x1234 id: 1, to: "[email protected]", from: "[email protected]">, <#Email:0x1224 id: 3, to: "[email protected]", from: "[email protected]">]

So basically I only want one of the records if it turns out to be a match when case is ignored. Is this possible to do in a one-liner and have the database return the right records or is there another clever way of doing this? I am hitting a wall for some reason and would appreciate your help!

Upvotes: 0

Views: 320

Answers (1)

Cody Caughlan
Cody Caughlan

Reputation: 32748

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

Upvotes: 2

Related Questions