Skip to content

Instantly share code, notes, and snippets.

@markfeedly

markfeedly/pesky

Last active August 29, 2015 14:12
Show Gist options
  • Save markfeedly/314574054e08009389ba to your computer and use it in GitHub Desktop.
Save markfeedly/314574054e08009389ba to your computer and use it in GitHub Desktop.
Download ZIP
pesky sql not enough rails knowledge
Raw
pesky
If we have nils for taggable_id in tag_items, then when I go to
http://localhost:3000/collection_items/b
the server log looks OK, like this (hey I only put this in for comparison,
no need to read it (yet?)), skip on below
~/01rails/social-museum 0 $ rails s
=> Booting Puma
=> Rails 4.1.4 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Notice: server is listening on all interfaces (0.0.0.0). Consider using 127.0.0.1 (--binding option)
=> Ctrl-C to shutdown server
Puma 2.9.0 starting...
* Min threads: 0, max threads: 16
* Environment: development
* Listening on tcp://0.0.0.0:3000
ActiveRecord::SchemaMigration Load (1.0ms) SELECT "schema_migrations".* FROM "schema_migrations"
User Load (1.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
CollectionItem Load (2.0ms) SELECT "collection_items".* FROM "collection_items" INNER JOIN "titles" ON "titles"."titleable_id" = "collection_items"."id" AND "titles"."titleable_type" = 'CollectionItem' WHERE "titles"."slug" = 'b' ORDER BY "collection_items"."id" ASC LIMIT 1
Title Load (0.6ms) SELECT "titles".* FROM "titles" WHERE "titles"."titleable_id" = $1 AND "titles"."titleable_type" = $2 LIMIT 1 [["titleable_id", 2], ["titleable_type", "CollectionItem"]]
Category Load (1.0ms) SELECT "categories".* FROM "categories" INNER JOIN "category_items" ON "categories"."id" = "category_items"."category_id" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Tag Load (1.0ms) SELECT "tags".* FROM "tags" INNER JOIN "tag_items" ON "tags"."id" = "tag_items"."tag_id" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
Subscription Exists (0.7ms) SELECT 1 AS one FROM "subscriptions" WHERE "subscriptions"."subscribable_id" = $1 AND "subscriptions"."subscribable_type" = $2 AND "subscriptions"."user_id" = 1 LIMIT 1 [["subscribable_id", 2], ["subscribable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT 1 AS one FROM "subscriptions" WHERE "subscriptions"."subscribable_id" = $1 AND "subscriptions"."subscribable_type" = $2 AND "subscriptions"."user_id" = 1 LIMIT 1 [["subscribable_id", 2], ["subscribable_type", "CollectionItem"]]
Comment Exists (0.9ms) SELECT 1 AS one FROM "comments" WHERE "comments"."commentable_id" = $1 AND "comments"."commentable_type" = $2 LIMIT 1 [["commentable_id", 2], ["commentable_type", "CollectionItem"]]
Resource Load (0.9ms) SELECT "resources".* FROM "resources" INNER JOIN "resource_usages" ON "resources"."id" = "resource_usages"."resource_id" WHERE "resource_usages"."resourceable_id" = $1 AND "resource_usages"."resourceable_type" = $2 [["resourceable_id", 2], ["resourceable_type", "CollectionItem"]]
Secretary::Version Load (1.0ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
TagItem Load (0.6ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
(0.4ms) BEGIN
SQL (0.8ms) UPDATE "tag_items" SET "taggable_id" = NULL WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 AND "tag_items"."id" IN (1, 2, 3) [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
(2.2ms) COMMIT
CategoryItem Load (0.4ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.7ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 3) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
Secretary::Version Load (0.6ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
TagItem Load (0.6ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.7ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 2) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
CACHE (0.1ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.5ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 1) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
Tag Load (0.8ms) SELECT "tags".* FROM "tags" WHERE "tags"."id" = 3 ORDER BY "tags"."id" ASC LIMIT 1
Tag Load (0.8ms) SELECT "tags".* FROM "tags" WHERE "tags"."id" = 2 ORDER BY "tags"."id" ASC LIMIT 1
method=GET path=/collection_items/b format=html controller=collection_items action=show status=200 duration=1139.52 view=871.08 db=60.61 site=http://localhost:3000 user=1 params/id='b' user_agent=Mozilla/5.0_(X11;_Linux_x86_64)_AppleWebKit/537.36_(KHTML,_like_Gecko)_Chrome/39.0.2171.71_Safari/537.36 browser=Chrome platform=X11 browser_version=39.0.2171.71 browser_combined=Chrome-39.0.2171.71 platform_combined=X11-Chrome-39.0.2171.71 remote_ip=127.0.0.1 referer=None
------------------------------------------------------------
Let's dive into the console at this stage.
(note, although for the example given and pic in the email, there are only two tags in play, there is an formerly used tag knocking around in the tags table from before, no big deal)
~/01rails/social-museum 0 $ rails c
Loading development environment (Rails 4.1.4)
[1] pry(main)> TagItem.all
TagItem Load (3.3ms) SELECT "tag_items".* FROM "tag_items"
=> [#<TagItem id: 1, taggable_id: nil, taggable_type: "CollectionItem", tag_id: 1, created_at: "2014-12-27 21:44:23", updated_at: "2014-12-27 21:59:51">,
#<TagItem id: 2, taggable_id: nil, taggable_type: "CollectionItem", tag_id: 2, created_at: "2014-12-27 22:12:18", updated_at: "2014-12-27 22:16:56">,
#<TagItem id: 3, taggable_id: nil, taggable_type: "CollectionItem", tag_id: 3, created_at: "2014-12-27 22:25:48", updated_at: "2014-12-27 23:04:23">]
[2] pry(main)> Tag.all
Tag Load (1.0ms) SELECT "tags".* FROM "tags"
=> [#<Tag id: 1, name: "t00000000000000000">, #<Tag id: 2, name: "t1111111111111111">, #<Tag id: 3, name: "xxxxxxxxxxxxx">]
[3] pry(main)> CollectionItem.all
CollectionItem Load (0.9ms) SELECT "collection_items".* FROM "collection_items"
=> [#<CollectionItem id: 2, description: "", location: "b", item_number: "b", created_at: "2014-12-27 22:11:58", updated_at: "2014-12-27 22:25:48", lock_version: 2, user_id: nil>]
[4] pry(main)> TagItem.each{|it| it.taggable_id=2; it.save}
NoMethodError: undefined method `each' for #<Class:0x007fe2b39c1da8>
from /home/mark/.gem/ruby/2.1.2/gems/activerecord-4.1.4/lib/active_record/dynamic_matchers.rb:26:in `method_missing'
[5] pry(main)> TagItem.all.each{|it| it.taggable_id=2; it.save}
TagItem Load (1.2ms) SELECT "tag_items".* FROM "tag_items"
(0.4ms) BEGIN
SQL (8.9ms) UPDATE "tag_items" SET "taggable_id" = $1, "updated_at" = $2 WHERE "tag_items"."id" = 1 [["taggable_id", 2], ["updated_at", "2014-12-28 11:57:30.329599"]]
(4.2ms) COMMIT
(0.2ms) BEGIN
SQL (0.6ms) UPDATE "tag_items" SET "taggable_id" = $1, "updated_at" = $2 WHERE "tag_items"."id" = 2 [["taggable_id", 2], ["updated_at", "2014-12-28 11:57:30.353797"]]
(2.1ms) COMMIT
(0.2ms) BEGIN
SQL (0.6ms) UPDATE "tag_items" SET "taggable_id" = $1, "updated_at" = $2 WHERE "tag_items"."id" = 3 [["taggable_id", 2], ["updated_at", "2014-12-28 11:57:30.360529"]]
(2.1ms) COMMIT
=> [#<TagItem id: 1, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 1, created_at: "2014-12-27 21:44:23", updated_at: "2014-12-28 11:57:30">,
#<TagItem id: 2, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 2, created_at: "2014-12-27 22:12:18", updated_at: "2014-12-28 11:57:30">,
#<TagItem id: 3, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 3, created_at: "2014-12-27 22:25:48", updated_at: "2014-12-28 11:57:30">]
[6] pry(main)> TagItem.all
TagItem Load (0.7ms) SELECT "tag_items".* FROM "tag_items"
=> [#<TagItem id: 1, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 1, created_at: "2014-12-27 21:44:23", updated_at: "2014-12-28 11:57:30">,
#<TagItem id: 2, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 2, created_at: "2014-12-27 22:12:18", updated_at: "2014-12-28 11:57:30">,
#<TagItem id: 3, taggable_id: 2, taggable_type: "CollectionItem", tag_id: 3, created_at: "2014-12-27 22:25:48", updated_at: "2014-12-28 11:57:30">]
[7] pry(main)> CollectionItem.first.tags
CollectionItem Load (1.3ms) SELECT "collection_items".* FROM "collection_items" ORDER BY "collection_items"."id" ASC LIMIT 1
Tag Load (0.9ms) SELECT "tags".* FROM "tags" INNER JOIN "tag_items" ON "tags"."id" = "tag_items"."tag_id" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
=> [#<Tag id: 1, name: "t00000000000000000">, #<Tag id: 2, name: "t1111111111111111">, #<Tag id: 3, name: "xxxxxxxxxxxxx">]
[8] pry(main)>
All looks good huh? I should see a tag for CollectionItem b on refreshing my CollectionItem page for b?
Well, no; read on please
---------------------------------------------------------------
When I again visit http://localhost:3000/collection_items/b I get this server output
showing how a db access from Version is wiping out those non-nil taggable_ids,
see the lines inside ===HERE== ==END HERE====
~/01rails/social-museum 0 $ rails s
=> Booting Puma
=> Rails 4.1.4 application starting in development on http://0.0.0.0:3000
=> Run `rails server -h` for more startup options
=> Notice: server is listening on all interfaces (0.0.0.0). Consider using 127.0.0.1 (--binding option)
=> Ctrl-C to shutdown server
Puma 2.9.0 starting...
* Min threads: 0, max threads: 16
* Environment: development
* Listening on tcp://0.0.0.0:3000
ActiveRecord::SchemaMigration Load (1.0ms) SELECT "schema_migrations".* FROM "schema_migrations"
User Load (1.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
CollectionItem Load (2.0ms) SELECT "collection_items".* FROM "collection_items" INNER JOIN "titles" ON "titles"."titleable_id" = "collection_items"."id" AND "titles"."titleable_type" = 'CollectionItem' WHERE "titles"."slug" = 'b' ORDER BY "collection_items"."id" ASC LIMIT 1
Title Load (0.6ms) SELECT "titles".* FROM "titles" WHERE "titles"."titleable_id" = $1 AND "titles"."titleable_type" = $2 LIMIT 1 [["titleable_id", 2], ["titleable_type", "CollectionItem"]]
Category Load (1.0ms) SELECT "categories".* FROM "categories" INNER JOIN "category_items" ON "categories"."id" = "category_items"."category_id" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Tag Load (1.0ms) SELECT "tags".* FROM "tags" INNER JOIN "tag_items" ON "tags"."id" = "tag_items"."tag_id" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
Subscription Exists (0.7ms) SELECT 1 AS one FROM "subscriptions" WHERE "subscriptions"."subscribable_id" = $1 AND "subscriptions"."subscribable_type" = $2 AND "subscriptions"."user_id" = 1 LIMIT 1 [["subscribable_id", 2], ["subscribable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT 1 AS one FROM "subscriptions" WHERE "subscriptions"."subscribable_id" = $1 AND "subscriptions"."subscribable_type" = $2 AND "subscriptions"."user_id" = 1 LIMIT 1 [["subscribable_id", 2], ["subscribable_type", "CollectionItem"]]
Comment Exists (0.9ms) SELECT 1 AS one FROM "comments" WHERE "comments"."commentable_id" = $1 AND "comments"."commentable_type" = $2 LIMIT 1 [["commentable_id", 2], ["commentable_type", "CollectionItem"]]
Resource Load (0.9ms) SELECT "resources".* FROM "resources" INNER JOIN "resource_usages" ON "resources"."id" = "resource_usages"."resource_id" WHERE "resource_usages"."resourceable_id" = $1 AND "resource_usages"."resourceable_type" = $2 [["resourceable_id", 2], ["resourceable_type", "CollectionItem"]]
============================= HERE =========================================
Secretary::Version Load (1.0ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
TagItem Load (0.6ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
(0.4ms) BEGIN
SQL (0.8ms) UPDATE "tag_items" SET "taggable_id" = NULL WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 AND "tag_items"."id" IN (1, 2, 3) [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
(2.2ms) COMMIT
============================= END HERE =========================================
CategoryItem Load (0.4ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.7ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 3) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
Secretary::Version Load (0.6ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
TagItem Load (0.6ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.7ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 2) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
CACHE (0.1ms) SELECT "tag_items".* FROM "tag_items" WHERE "tag_items"."taggable_id" = $1 AND "tag_items"."taggable_type" = $2 [["taggable_id", 2], ["taggable_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "category_items".* FROM "category_items" WHERE "category_items"."categorisable_id" = $1 AND "category_items"."categorisable_type" = $2 [["categorisable_id", 2], ["categorisable_type", "CollectionItem"]]
Secretary::Version Load (0.5ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 AND (version_number <= 1) ORDER BY version_number DESC [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."versioned_id" = $1 AND "versions"."versioned_type" = $2 ORDER BY "versions"."version_number" DESC LIMIT 1 [["versioned_id", 2], ["versioned_type", "CollectionItem"]]
CACHE (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 1]]
Tag Load (0.8ms) SELECT "tags".* FROM "tags" WHERE "tags"."id" = 3 ORDER BY "tags"."id" ASC LIMIT 1
Tag Load (0.8ms) SELECT "tags".* FROM "tags" WHERE "tags"."id" = 2 ORDER BY "tags"."id" ASC LIMIT 1
method=GET path=/collection_items/b format=html controller=collection_items action=show status=200 duration=1139.52 view=871.08 db=60.61 site=http://localhost:3000 user=1 params/id='b' user_agent=Mozilla/5.0_(X11;_Linux_x86_64)_AppleWebKit/537.36_(KHTML,_like_Gecko)_Chrome/39.0.2171.71_Safari/537.36 browser=Chrome platform=X11 browser_version=39.0.2171.71 browser_combined=Chrome-39.0.2171.71 platform_combined=X11-Chrome-39.0.2171.71 remote_ip=127.0.0.1 referer=None
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment