[openstreetmap/openstreetmap-website] Add sorting and filtering functionality to user notes page (PR #5255)

[Emin Kocan]

> Besides the [double-join db query](https://github.com/openstreetmap/openstreetmap-website/pull/5255#discussion_r1801064970), there's couple more issues with the interaction type: ["blue on blue" colors](https://github.com/openstreetmap/openstreetmap-website/pull/4700#issuecomment-2221682594) and whether the submitted/commented classification makes sense at all (what if I closed a note without a comment?). I wanted to try opened/closed/commented instead.

First of all, thank you for taking time and effort reviewing this. Few things I would like to reassess:
 - Related to the commented/submitted criteria we could use `EXISTS` subqueries which removes the need for a double joins, potentially making the query simpler and more performant. I could try testing this in terms of performance with large number of mocked entries so we can better get the idea of how this would affect the performance.

```ruby
  scope :filter_by_note_type, lambda { |note_type, user_id|
    case note_type
    when "commented"
      where("EXISTS (SELECT 1 FROM note_comments WHERE note_comments.note_id = notes.id AND (note_comments.author_id != ? OR note_comments.author_id IS NULL))", user_id)
    when "submitted"
      where("EXISTS (SELECT 1 FROM note_comments WHERE note_comments.note_id = notes.id AND note_comments.author_id = ? AND note_comments.id = (SELECT MIN(nc.id) FROM note_comments nc WHERE nc.note_id = notes.id))", user_id)
    else
      all
    end
  }
```

- Regarding the open/closed/commented differentiation instead of commented/submitted - It makes sense to me too but I would also like to hear the @tomhughes @gravitystorm opinions on this too, so we could all sync on right approach for this.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/5255#issuecomment-2426960898
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/5255/c2426960898 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20241021/f12d1772/attachment.htm>