Postgres选择一个concat字段，然后在Rails中像它一样

本文介绍了Postgres选择一个concat字段，然后在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.