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"
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
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
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
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}
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!
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])
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.