[openstreetmap/openstreetmap-website] Copies notes details from first comments to notes table (PR #5667)

[Nenad Vujicic]

nenad-vujicic left a comment (openstreetmap/openstreetmap-website#5667)

> I think this will do the job, obviously with batches of note IDs in the CTE expression:
> 

Thanks! I think I fixed it (with the latest push). Here is the code:
```ruby
Note.in_batches(:of => 1000) do |notes|
  note_ids = notes.pluck(:id)

  sql_query = <<-SQL.squish
    WITH first_comment AS(
      SELECT DISTINCT ON (note_id) *
      FROM note_comments
      WHERE note_id BETWEEN #{note_ids.min} AND #{note_ids.max}
      ORDER BY note_id, id
    )
    UPDATE notes
    SET description = first_comment.body,
        user_id = first_comment.author_id,
        user_ip = first_comment.author_ip
    FROM first_comment
    WHERE first_comment.note_id = notes.id
      AND first_comment.event = 'opened';
  SQL

  ActiveRecord::Base.connection.execute(sql_query)
end
```

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

Message ID: <openstreetmap/openstreetmap-website/pull/5667/c2659190584 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20250214/4a56eb87/attachment.htm>