How can I do case-insensitive ordering in DataMapper with Postgres?

Question

So, I'm trying to order my results from DataMapper without regard to case. Right now, I do something like Item.all(:order => :artist). I tried doing Item.all(:order => "LOWER(artist)"), but I just get an error that LOWER(artist) isn't a property of Item. Is there a good way to do this with DataMapper?

Answer 1

DataMapper might not support raw sql in the ordering clause. https://github.com/datamapper/dm-core/pull/11 has more information. If you really need it, you could apply that patch to your version of DataMapper.

Even if DataMapper did support it, you would also need an index for lower(artist) to make the ordering efficient.

What you probably want to do instead is use the postgresql citext data type for the artist column. It provides case-insensitive search and ordering. http://www.postgresql.org/docs/9.1/static/citext.html

If you are lucky enough to be using postgresql 9.1, the below should work great. If you're using < 9.1, you'd need to load the citext module differently, the postgresql docs should show you how.

create extension citext;

create table test (
  name citext
);

insert into test values ('a'), ('b'), ('X'), ('m'), ('D');

select * from test order by name;

 name 
------
 a
 b
 D
 m
 X