how can I filter has_many records by type and created_at?

Question

(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
If two notes of the same type were written today, grab those.
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
Grab all notes
Select only the most recent notes by type.
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.

Answer 1

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"