Postgres SELECT a concat field and ILIKE it in Rails

Question

Pretty simple, trying to do this

 SELECT  (artist_name || ' ' || name) as full_name FROM "songs"  WHERE "songs"."working" = 't' AND (full_name ILIKE('%Jack Beats%')) AND (full_name ILIKE('%Epidemic%')) AND (full_name ILIKE('%Dillon Francis%')) ORDER BY songs.published_at asc LIMIT 1

But I get

ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "full_name" does not exist

I've tried adding the table name before the stations with no effect.

Answer 1

As sub_stantial mentions in the comments, you can't reference an alias from a SELECT in your WHERE clause. You can use a derived table as dwurf suggests but derived tables in Rails are a bit messy. You could expand your concatenation inside your WHERE instead:

Song.where(:working => true)
    .where("artist_name || ' ' || name ILIKE ?", '%Jack Beats%')
    .where("artist_name || ' ' || name ILIKE ?", '%Epidemic%')
    .where("artist_name || ' ' || name ILIKE ?", '%Dillon Francis%')
    .order('songs.published_at asc')
    .limit(1)

And if you're doing this sort of thing a lot, a named scope might be useful:

class Song < ActiveRecord::Base
    #...
    def self.full_name_like(name)
        where("artist_name || ' ' || name ILIKE ?", "%#{name}%")
    end
end

and then:

Song.where(:working => true)
    .full_name_like('Jack Beats')
    .full_name_like('Epidemic')
    .full_name_like('Dillon Francis')
    .order('songs.published_at asc')
    .limit(1)

If your application is going to be doing a lot of ILIKE searches like this then you might want to look into a full-text search system: LIKE queries lead to table scans and table scans lead to sadness.