我有一個位置模型，表格看起來像
| ID | 姓名 | 酒 | IP地址 | created_at | 更新時間 |
|---|---|---|---|---|---|
| 0 | 默認 | 0 | 0.0.0.0/0 | 2021-11-08 11:54:26.822623 | 2021-11-08 11:54:26.822623 |
| 1 | 行政 | 1 | 10.108.150.143 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 2 | V122 | 122 | 10.108.150.122 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 3 | V123 | 123 | 10.108.150.123 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 4 | V124 | 124 | 10.108.150.124 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 5 | V122 | 122 | 10.108.150.122 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 6 | V125 | 122 | 10.108.150.125 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
我在 Location 模型中的方法
def self.find_all_non_duplicate
return self.find(:all, :conditions => "id <> 1")
end
我想獲取位置表的所有條目，除了 id = 1 的條目，該條目僅包含基于ip_address列的許多重復項的第一個條目。
由于id = 2 和 id = 5 的ip_address是重復的。我想保留許多重復項的第一個條目，即id = 2。
預期的結果是
| ID | 姓名 | 酒 | IP地址 | created_at | 更新時間 |
|---|---|---|---|---|---|
| 0 | 默認 | 0 | 0.0.0.0/0 | 2021-11-08 11:54:26.822623 | 2021-11-08 11:54:26.822623 |
| 2 | V122 | 122 | 10.108.150.122 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 3 | V123 | 123 | 10.108.150.123 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 4 | V124 | 124 | 10.108.150.124 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
| 6 | V125 | 122 | 10.108.150.125 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885 |
id 為 1 和 5 的條目將被忽略
uj5u.com熱心網友回復：
正如@engineersmnky所指出的，您需要的是distinct on最近在這里向 RoR 提出的建議，但尚未合并。在原始 SQL 中，它將如下所示：
select distinct on (ip_address) *
from test
where id<>1
order by ip_address,created_at;
這將轉化為 RoR
self.where("id <> 1").distinct_on(:ip_address)
或者，直到新功能被接受：
self.where("id <> 1").select("distinct on (ip_address) *")
完整的資料庫端測驗：
drop table if exists test cascade;
create table test (
id serial primary key,
name text,
vin integer,
ip_address inet,
created_at timestamp,
updated_at timestamp);
insert into test
(id,name,vin,ip_address,created_at,updated_at)
values
(0,'default', 0,'0.0.0.0/0'::inet,'2021-11-08 11:54:26.822623'::timestamp,'2021-11-08 11:54:26.822623'::timestamp),
(1,'admin', 1,'10.108.150.143'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(2,'V122', 122,'10.108.150.122'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(3,'V123', 123,'10.108.150.123'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(4,'V124', 124,'10.108.150.124'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(5,'V122', 122,'10.108.150.122'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(6,'V125', 122,'10.108.150.125'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp);
select distinct on (ip_address) *
from test where id<>1
order by ip_address,created_at;
-- id | name | vin | ip_address | created_at | updated_at
------ --------- ----- ---------------- ---------------------------- ----------------------------
-- 0 | default | 0 | 0.0.0.0/0 | 2021-11-08 11:54:26.822623 | 2021-11-08 11:54:26.822623
-- 2 | V122 | 122 | 10.108.150.122 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885
-- 3 | V123 | 123 | 10.108.150.123 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885
-- 4 | V124 | 124 | 10.108.150.124 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885
-- 6 | V125 | 122 | 10.108.150.125 | 2021-11-08 11:54:26.82885 | 2021-11-08 11:54:26.82885
--(5 rows)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/374998.html
標籤：红宝石轨道 PostgreSQL的 ruby-on-rails-3 活动记录