英文:
how can I filter has_many records by type and created_at?
问题
(作为一个初学者的 Ruby/Rails 学习者提问)
一个群组有多个笔记。笔记有一个 type_id。
如果 type_id 存在，我想要按照 type_id 来过滤群组中的笔记，同时还要按创建时间来选择最早的笔记。
模式 1：如果 type_id 存在 <br />
如果同一类型的两个笔记今天都写了，那就选择它们。<br />
如果同一类型的两个笔记在不同的日期写的，那就选择最新的那个。
模式 2：如果 type_id 不存在 <br />
选择所有的笔记 <br />
只选择每种类型的最新的笔记。<br />
如果同类型的笔记存在但是在不同的日期，就过滤掉它们。
我能够实现模式 1（虽然代码看起来有点混乱？需要调用两次）
if params[:note_type_id].present?
earliest_date = notes
.where(notes_type_id: params[:notes_type_id])
.minimum(:created_at)
notes
.where(notes_type_id: params[:notes_type_id])
.where(created_at: earliest_date.all_day)
我不确定如何开始实现模式 2。
我在考虑不在数据库层面进行处理，而是使用 Ruby 来过滤。
欢迎任何建议。
英文:
(asking as a novice ruby/rails learner)
A group has_many notes. Notes has a type_id.
if type_id exists, I want to filter the notes from group by type_id, but also where created_at is earliest.
Pattern 1: If type_id exists <br />
If two notes of the same type were written today, grab those. <br />
If two notes of the same type were written on different days, grab the latest/most recent.
Pattern 2: If type_id doesn't exist <br />
Grab all notes <br />
Select only the most recent notes by type. <br />
If same type notes exists but on different days, filter them out.
I was able to get Pattern 1 (although quite dirty? i need to call twice)
if params[:note_type_id].present?
earliest_date = note
.where(notes_type_id: params[:notes_type_id])
.minimum(:created_at)
notes
.where(notes_type_id: params[:notes_type_id])
.where(created_at: earliest_date.all_day)
I'm not sure on how to start with Pattern 2.
I'm thinking not to do it on the DB side but just use ruby to filter.
All advice appreciated.
答案1
得分: 1
> Pattern 1: 如果存在 type_id
>
> 如果今天写了两个相同类型的笔记，请获取它们。
notes_of_type = note.where(notes_type_id: params[:notes_type_id])
notes_today = notes_of_type
.where(created_at: Date.today.all_day)
.limit(2) # ?? 模糊的问题 -- 你是想获取最多2个，还是“所有”？
> 如果不同天写了两个相同类型的笔记，请获取最早/最近的。
"最早" 和 "最近" 意思相反。我假设你是指 最近/最新的。
在这种情况下，你可以这样做：
if notes_today.none?
notes_of_type.order(created_at: :desc).first
end
> Pattern 2: 如果不存在 type_id
>
> 获取所有笔记
>
> 只选择每种类型的最近笔记。
>
> 如果存在相同类型的笔记但在不同天，请过滤掉它们。
最后的要求对我来说似乎是多余的。如果我们只选择每种类型的最近笔记，那就不需要额外的过滤了？
有几种方法。以下是一种通用策略，使用窗口函数：
select_sql = Note.select('*, dense_rank() OVER (PARTITION BY notes_type_id ORDER BY created_at desc) as recent_rank')
Note.from(select_sql, 'notes').where(recent_rank: 1) # 或者获取 "2 最近的"
注意：原文中的代码可能包含一些 HTML 编码字符（如>和"），我已经将它们还原为普通的文本字符。
英文:
> Pattern 1: If type_id exists
>
> If two notes of the same type were written today, grab those.
notes_of_type = note.where(notes_type_id: params[:notes_type_id])
notes_today = notes_of_type
.where(created_at: Date.today.all_day)
.limit(2) # ?? Ambiguous question -- did you mean to only get a maximum of 2, or "all of them"?
> If two notes of the same type were written on different days, grab the earliest/most recent.
"Earliest" and "most recent" mean the exact opposite. I assume you mean latest/most recent.
In which case, you could do this:
if notes_today.none?
notes_of_type.order(created_at: :desc).first
end
> Pattern 2: If type_id doesn't exist
>
> Grab all notes
>
> Select only the most recent notes by type.
>
> If same type notes exists but on different days, filter them out.
The last requirement sounds redundant to me. If we're only selecting the most recent note by type, then no additional filtering would be needed?
There are a few ways. Here is a generalised strategy, using a window function:
select_sql = Note.select('*, dense_rank() OVER (PARTITION BY notes_type_id ORDER BY created_at desc) as recent_rank')
Note.from(select_sql, 'notes').where(recent_rank: 1) # Or get the "2 most recent"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论