Function to return the minimum or maximum value per row with MySQL
MySQL's MIN and MAX functions are for aggregations only. This will not work and produce an error:
SELECT id, MIN(birthday, '1978-01-01') FROM users;
In order to compute the minimum or maximum value for the current row, use
LEAST
Show archive.org snapshot
and
GREATEST
Show archive.org snapshot
instead:
SELECT id, LEAST(birthday, '1978-01-01') FROM users;
Related cards:
Use a Bash function to alias the rake command to Spring binstubs or "bundle exec" fallback
There are different ways to run rake:
- On Rails 4.1+ projects, you have Spring and its binstubs which dramatically improve boot-up time for Rake and similar. You need to run
bin/raketo use them. - On older projects, you want to run "bundle ex...
PostgreSQL: How to add/remove/modify array values (and how to replace 1 value with multiple values)
PostgreSQL's array data type is pretty useful, but manipulating values of arrays can be awkward because of its syntax.
Consider the following users table which each example below will sta...
MySQL: How to create columns like "bigint" or "longtext" in Rails migrations, and what :limit means for column migrations
Rails understands a :limit options when you create columns in a migration. Its meaning depends on the column type, and sometimes the supplied value.
[The documentation](http://apidock.com/rails/ActiveRecord/ConnectionAdapters/TableDefinition/co...
How to create a user with all privileges to MariaDB
Create a user without password (recommended)
Replace newuser with your desired username:
mysql -uroot -p
CREATE USER 'newuser'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
exit;...
Use find_in_batches or find_each to deal with many records efficiently
Occasionally you need to do something directly on the server -- like having all records recalculate something that cannot be done in a migration because it takes a long time.
Let's say you do something like this:
Project.all.each(&:recal...
Fun with Ruby: Returning in blocks "overwrites" outside return values
In a nutshell: return statements inside blocks cause a method's return value to change. This is by design (and probably not even new ...
Installing multiple MySQL versions on the same Linux with mysql-sandbox
Ubuntu has a package mysql-sandbox that lets you install multiple MySQL versions into your user home:
- Install
mysql-sandbox
sudo apt install mysql-sandbox
- Download the version of MySQL you want to use from mysql.com:
<https://...
JavaScript: Testing the type of a value
Checking if a JavaScript value is of a given type can be very confusing:
- There are two operators
typeofandinstanceofwhich work very differently. - JavaScript has some primitive types, like string literals, that are not objects (as oppose...
How to use pessimistic row locks with ActiveRecord
When requests arrive at the application servers simultaneously, weird things can happen. Sometimes, this can also happen if a user double-clicks on a button, for example.
This often leads to problems, as two object instances are modified in paral...
How to coordinate distributed work with MySQL's GET_LOCK
The linked article explains how to get a database-wide lock without creating table rows:
This article explains how I replaced file-based methods to...