Postgres选择一个concat字段，然后在Rails中像它一样 [英] Postgres SELECT a concat field and ILIKE it in Rails

问题描述

非常简单，尝试执行此操作

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

但是我得到了

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

我尝试在工作站之前添加表名，但没有任何效果。

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

推荐答案

正如注释中的实质性提及，您不能在WHERE子句中引用SELECT中的别名。您可以按照dwurf的建议使用派生表，但是Rails中的派生表有点混乱。您可以改为在WHERE内扩展串联：

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

，然后：

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)

如果您的应用程序将要进行大量这样的ILIKE搜索，那么您可能希望研究全文搜索系统：LIKE查询会导致表扫描和表格扫描会导致悲伤。

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.

这篇关于Postgres选择一个concat字段，然后在Rails中像它一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！