3 posts about #sql

Count similar items in an array in Ruby

Before ruby 2.7, you would have to do some hacky code like:

array.inject({}) {|f, v| f[v] ||= 0; f[v] += 1 ; f }

But starting with Ruby 2.7, there's a nicer way:

irb: [1, 2, 3, 4, 4, 5, 5].tally
=> {1=>1, 2=>1, 3=>1, 4=>2, 5=>2}

Learned by Edwin Cruz on Mar 11, 2021

973983 adforcodereview v3 0211021 c02 021121

Testing and sharing simple SQL queries online

If you want to test and share a simple SQL query, take a look at http://sqlize.com. Keep in mind you'll need to create the tables you need in the same script and you're only allowed to create TEMPORARY tables. You can run your query and see the results below it right away. Then, you can generate a permalink and share your query with everyone!

Learned by kevin-perez on Mar 10, 2021

973983 adforcodereview v3 0211021 c02 021121

Monthly streak display using SQL CTE in Rails

How to make a streak display like 750words.com


The database can do the heavy lifting for this kind of thing. A CTE (Common Table Expression) in a WITH clause to give me the set of days of the month that I would fill in with the completion data using a LEFT JOIN to make sure I got rows back even when there was no corresponding data on the right side.

# app/models/streak.rb
Streak = Struct.new(:user, :date) do
  # If you're wondering why we didn't use class extension syntax of Struct see the following link
  # https://tiagoamaro.com.br/2016/03/05/superclass-mismatch-structs-and-unicorn/
  SQL = "WITH dates(d) AS (
            SELECT generate_series(
              (date ?)::timestamp,
              (date ?)::timestamp,
              interval '1 day')
            SELECT dates.d::date created_at, count(e.id) count FROM dates
            LEFT JOIN entries e on dates.d::date = e.created_at::date AND e.user_id = ?
            GROUP BY dates.d::date
            ORDER BY dates.d::date"

  def calendar
    Entry.find_by_sql([SQL, date.beginning_of_month, date.end_of_month, user.id])

I'm knowingly abusing ActiveRecord by shoving the data I want to represent into Entry objects. I can be kinder to my future self and other maintainers by subsequently wrapping the returned objects into something more descriptive like StreakMonth or whatever.

Learned by obie-fernandez on Feb 10, 2021

973983 adforcodereview v3 0211021 c02 021121