Logo

TODAY I LEARNED

5 posts about #sql

ON DUPLICATE KEY UPDATE

When you're doing an INSERT query, you could be trying to insert a row containing a primary key that already exists in the table. Instead of doing a previous query to see if the key exists or not, you could try ON DUPLICATE KEY UPDATE.

INSERT INTO table (
  field1, 
  field2
) 
VALUES (
  "foo", 
  "bar"
) 
ON DUPLICATE KEY UPDATE 
  field1="foo", 
  field2="bar"

Learned by kevin-perez on Jul 6, 2021

973983 adforcodereview v3 0211021 c02 021121

How to understand the SQL behind an ActiveRecord Query

Quick example, to see and understand better how an ActiveRecord query using scopes or methods in the model works at the SQL level and how to run it, all in the same example

Ruby version

Let say you have a database with users and you have some scopes to filter by non-demo, active, and completed providers with a specific Role (CanCanCan behind the scenes) :

User.providers.real.active.completed

Let's look into the SQL version:

If we run this command

irb(main):007:0> User.providers.real.active.completed.to_sql

We would get this:

=> "SELECT \"users\".* FROM \"users\" INNER JOIN \"users_roles\" ON \"users_roles\".\"user_id\" = \"users\".\"id\" INNER JOIN \"roles\" ON \"roles\".\"id\" = \"users_roles\".\"role_id\" WHERE (roles.name = 'Provider') AND \"users\".\"demo_account\" = 'f' AND (\"users\".\"organization_id\" NOT IN (SELECT \"organizations\".\"id\" FROM \"organizations\" WHERE \"organizations\".\"demo_account\" = 't' ORDER BY \"organizations\".\"name\" ASC)) AND \"users\".\"is_active\" = 't' AND \"users\".\"signup_state\" = 'Completed'"

Then we just need to replace the invalid characters such as \ and translate that into a SQL version like this:

SUMMARY_PROVIDERS_REPORT_SQL = <<-SQL
WITH real_providers AS
  ( SELECT users.* FROM users
    INNER JOIN users_roles ON users_roles.user_id = users.id
    INNER JOIN roles ON roles.id = users_roles.role_id
    WHERE (roles.name = 'Provider') AND users.demo_account = 'f'
      AND (users.organization_id NOT IN (
        SELECT organizations.id FROM organizations WHERE organizations.demo_account = 't' ORDER BY organizations.name ASC)
      )
      AND users.is_active = 't' AND users.signup_state = 'Completed')
select real_providers.id, real_providers.name from real_providers
SQL

In order to run it in the Rails console for example:

report_results = ActiveRecord::Base.connection.execute(SUMMARY_PROVIDERS_REPORT_SQL)
report_results.entries

That will give you the id and name of all those valid providers

Learned by heriberto-perez on Apr 21, 2021

973983 adforcodereview v3 0211021 c02 021121

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:

array.tally
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

image

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])
  end
end

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