Weeknotes 12

  • I was caught off guard on a Zoom call when I leant on the armrest of my chair, and it promptly snapped and fell away with a loud clatter. On inspection, the bolt that tightens as part of the height adjustment mechanism had snapped. I cannot find a replacement part for less than £65.

  • I paired this week on a feature requiring some non-trivial database queries in our Rails monolith. To calculate some statistics, we needed to filter rows based on a value in the last row for the same user (not necessarily the previous row in the table). As we use PostgreSQL, this is a perfect candidate for a window function, specifically LAG, in this case.

    select
      *,
      lag(value) over (partition by user_id order by created_at asc) as value
    from events
    

    Having not used window functions in ActiveRecord before and keen to avoid dropping down to SQL, it turns out simply using a custom select is sufficient and has the added benefit of easily being extracted into a scope.

    scope :include_previous_value, -> {
      select('*, lag(value) over (partition by user_id order by created_at asc) as value'
    }
    

    Unfortunately, we can’t use lag(value) in a where clause to filter on this value. To do this, we needed to write a small CTE. Luckily this turned out to be straightforward, especially when using the activerecord-cte gem. Our resulting scope now looked like this:

    scope :include_previous_value, -> {
      with(cte: 'select *, lag(value) over (partition by user_id order by created_at asc) as value from events')
        .from('cte as events')
    }
    

    Using a CTE without any where filters caused the query to run on the whole table, so we took a bit of a performance hit there. Fortunately, it’s okay for our use case, but I’m going to see if there’s a way to improve performance cleanly, without caving to find_by_sql (although it would be easy to delete).