davetapley

# Works, but a bit awkward:

def self.group_by_name(r)
  r.group_by &:name
end

Model.group_by_name(models.where(..))

# Would be nicer to have

davetapley

irb(main):050:0> user = User.find 1
[snip]
irb(main):051:0>  events = FactoryGirl.create_list :place_event, 3, user: user 
[snip]
irb(main):052:0> new_place =  FactoryGirl.create :place, user: user 
[snip]
irb(main):067:0> PlaceEvent.where("id IN (#{ events.collect(&:id).join(',') })").move_to new_place
[snip]
  SQL (1.1ms)  UPDATE "places" SET "place_events_count" = COALESCE("place_events_count", 0) + 1 WHERE "places"."id" = 1799211
  SQL (1.0ms)  UPDATE "places" SET "place_events_count" = COALESCE("place_events_count", 0) - 1 WHERE "places"."id" = 1799208

davetapley

redis 127.0.0.1:6379> LPUSH test, "hello"
(integer) 2
redis 127.0.0.1:6379> LLEN test
(integer) 0
redis 127.0.0.1:6379> RPOP test
(nil)
redis 127.0.0.1:6379> LPOP test
(nil)

davetapley

SELECT AVG(CAST(end_time AS time)) AS avg_end_time FROM "places" INNER JOIN "events" ON "events"."place_id" = "places"."id" AND events.category_id = 8 WHERE "places"."geocode_id" = 6096070 AND "places"."category_id" = 2 AND (place_events_count > 2) AND ((EXTRACT(EPOCH FROM end_time) - EXTRACT(EPOCH FROM start_time)) BETWEEN (4*3600) AND (12*3600) AND EXTRACT(HOUR FROM end_time) BETWEEN 11 AND 19) AND EXTRACT(DOW FROM end_time) IN (6,0);

  avg_end_time   
-----------------
 15:55:32.654876
(1 row)

davetapley

-- In first version WHERE is outside JOIN:

SELECT * FROM t1
  INNER JOIN t2 ON t1.t2_id = t2.id
WHERE t2.some_boolean;


-- In second version condition is lifted into JOIN condition

SELECT * FROM t1

davetapley

SELECT start_time, time_zone, start_time + (time_zone * INTERVAL '1 second') AS start_time_local FROM events LIMIT 1;

      start_time       | time_zone |   start_time_local    
-----------------------+-----------+-----------------------
 2012-10-25 11:53:37.5 |    -14400 | 2012-10-25 07:53:37.5

davetapley

-- from: https://wiki.postgresql.org/wiki/Aggregate_Histogram

CREATE OR REPLACE FUNCTION hist_sfunc (state INTEGER[], val REAL, min REAL, max REAL, nbuckets INTEGER) RETURNS INTEGER[] AS $$
DECLARE
  bucket INTEGER;
  i INTEGER;
BEGIN
  -- width_bucket uses nbuckets + 1 (!) and starts at 1.
  bucket := width_bucket(val, min, max, nbuckets - 1) - 1;
 

davetapley

WITH q_geocodes(name, id) AS (VALUES ('SF', 6096070),('NYC', 6164533))

SELECT
  q_geocodes.name,
  AVG(CAST(end_time + (time_zone * INTERVAL '1 sec') AS time)) AS mean_wake_time,

  COUNT(events.*) AS counts,
  histogram(EXTRACT(epoch FROM CAST(end_time + (time_zone * INTERVAL '1 sec') AS time)) :: real, EXTRACT(epoch FROM '04:00'::time) :: real, EXTRACT(epoch FROM '12:00'::time) :: real, 16) AS hist,

  EXTRACT(DOW FROM (end_time + time_zone * INTERVAL '1 sec' )) AS dow

davetapley

class CreateGroups < ActiveRecord::Migration
  def change
    create_table :groups do |t|
      t.integer :user_id
      t.string :name
      t.string :emails, array: true, null: false, default: []
      t.timestamps
    end

    add_index :groups, :user_id

davetapley

# CLONE

dave@lexi:~/projects$ git clone https://github.com/rails/rails-dev-box.git
Cloning into 'rails-dev-box'...
remote: Counting objects: 267, done.
remote: Compressing objects: 100% (185/185), done.
remote: Total 267 (delta 79), reused 222 (delta 52)
Receiving objects: 100% (267/267), 80.74 KiB, done.
Resolving deltas: 100% (79/79), done.