search

mbleigh / acts-as-taggable-on

A tagging plugin for Rails applications that allows for custom tagging along dynamic contexts.
http://mbleigh.lighthouseapp.com/projects/10116-acts-as-taggable-on
MIT License
4.98k stars 1.19k forks source link

Can't update counter cache or tag_list when manually creating tags #960

Open andrewdang17 opened 5 years ago

andrewdang17 commented 5 years ago

I've been experiencing a lot of performance issues when creating tags. See sql queries below:

ActsAsTaggableOn::Tagging Create (2555.1ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 3], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "instruments"], ["created_at", "2019-07-26 16:59:24.570124"]]
  ActsAsTaggableOn::Tag Load (1.0ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (1.0ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 3]]
  ActsAsTaggableOn::Tagging Exists (1.7ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 91], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "instruments"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2357.0ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 91], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "instruments"], ["created_at", "2019-07-26 16:59:27.132206"]]
  ActsAsTaggableOn::Tag Load (1.2ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 91], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (1.0ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 91]]
  ActsAsTaggableOn::Tagging Exists (3.3ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 13], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "instruments"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2306.6ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 13], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "instruments"], ["created_at", "2019-07-26 16:59:29.498042"]]
  ActsAsTaggableOn::Tag Load (1.0ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 13], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (0.9ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 13]]
  ActsAsTaggableOn::Tag Load (1.1ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  CACHE ActsAsTaggableOn::Tag Load (0.0ms)  SELECT "tags".* FROM "tags" WHERE (LOWER(name) = LOWER('aggressive') OR LOWER(name) = LOWER('burdened') OR LOWER(name) = LOWER('confident') OR LOWER(name) = LOWER('exciting') OR LOWER(name) = LOWER('gritty') OR LOWER(name) = LOWER('hopeful') OR LOWER(name) = LOWER('optimistic') OR LOWER(name) = LOWER('pumped') OR LOWER(name) = LOWER('sentimental') OR LOWER(name) = LOWER('vulnerable'))
  ActsAsTaggableOn::Tag Load (1.4ms)  SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = $1 AND "taggings"."taggable_type" = $2 AND (taggings.context = 'keywords' AND taggings.tagger_id IS NULL)  [["taggable_id", 62693], ["taggable_type", "SongVersion"]]
  ActsAsTaggableOn::Tagging Exists (1.8ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 251], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2295.1ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 251], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:31.820063"]]
  ActsAsTaggableOn::Tag Load (0.8ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 251], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (0.8ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 251]]
  ActsAsTaggableOn::Tagging Exists (5.1ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 72], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2317.5ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 72], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:34.124590"]]
  ActsAsTaggableOn::Tag Load (0.9ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 72], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (1.0ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 72]]
  ActsAsTaggableOn::Tagging Exists (4.0ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 8], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2643.3ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 8], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:36.451247"]]
  ActsAsTaggableOn::Tag Load (0.9ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 8], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (0.9ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 8]]
  ActsAsTaggableOn::Tagging Exists (1.8ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 78], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2472.5ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 78], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:39.101484"]]
  ActsAsTaggableOn::Tag Load (0.8ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 78], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (0.8ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 78]]
  ActsAsTaggableOn::Tagging Exists (1.8ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 10], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (2616.9ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 10], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:41.580429"]]
  ActsAsTaggableOn::Tag Load (1.1ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 10], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (1.8ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 10]]
  ActsAsTaggableOn::Tagging Exists (4.8ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 69], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]
  ActsAsTaggableOn::Tagging Create (3095.6ms)  INSERT INTO "taggings" ("tag_id", "taggable_id", "taggable_type", "context", "created_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["tag_id", 69], ["taggable_id", 62693], ["taggable_type", "SongVersion"], ["context", "keywords"], ["created_at", "2019-07-26 16:59:44.209669"]]
  ActsAsTaggableOn::Tag Load (1.0ms)  SELECT  "tags".* FROM "tags" WHERE "tags"."id" = $1 LIMIT $2  [["id", 69], ["LIMIT", 1]]
  ActsAsTaggableOn::Tag Update All (0.9ms)  UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) + 1 WHERE "tags"."id" = $1  [["id", 69]]
  ActsAsTaggableOn::Tagging Exists (3.4ms)  SELECT  1 AS one FROM "taggings" WHERE "taggings"."tag_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."taggable_id" = $3 AND "taggings"."context" = $4 AND "taggings"."tagger_id" IS NULL AND "taggings"."tagger_type" IS NULL LIMIT $5  [["tag_id", 70], ["taggable_type", "SongVersion"], ["taggable_id", 62693], ["context", "keywords"], ["LIMIT", 1]]

I've tried using activerecord-import to create tags in one query but then it looks like the counter cache and tag_list don't get updated, despite the tagging records existing.

ActsAsTaggableOn::Tagging Create Many Without Validations Or Callbacks (2343.4ms)  INSERT INTO "taggings" ("tag_id","context","taggable_id","taggable_type","created_at") VALUES (81,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(39,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(53,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(67,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(20,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(25,'keywords',62696,'SongVersion','2019-07-26 17:27:00.412145'),(21,'genres',62696,'SongVersion','2019-07-26 17:27:00.412145'),(1,'genres',62696,'SongVersion','2019-07-26 17:27:00.412145'),(26,'instruments',62696,'SongVersion','2019-07-26 17:27:00.412145'),(57,'instruments',62696,'SongVersion','2019-07-26 17:27:00.412145') RETURNING "id"
[7] pry(main)> sv.genres
  ActsAsTaggableOn::Tag Load (0.7ms)  SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = $1 AND "taggings"."taggable_type" = $2 AND "taggings"."context" = $3  [["taggable_id", 62696], ["taggable_type", "SongVersion"], ["context", "genres"]]
=> [#<ActsAsTaggableOn::Tag:0x00007fe3ad8bfb30 id: 21, name: "Folk", taggings_count: 11449>,
 #<ActsAsTaggableOn::Tag:0x00007fe3ad8bf950 id: 1, name: "Rock", taggings_count: 22630>]
[8] pry(main)> sv.genre_list
  ActsAsTaggableOn::Tagging Load (0.4ms)  SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = $1 AND "taggings"."taggable_type" = $2  [["taggable_id", 62696], ["taggable_type", "SongVersion"]]
=> []
[9] pry(main)> sv.cached_genre_list
=> ""

[11] pry(main)> ActsAsTaggableOn::Tagging.where(taggable_id: 62696, taggable_type: 'SongVersion')
  ActsAsTaggableOn::Tagging Load (0.5ms)  SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = $1 AND "taggings"."taggable_type" = $2  [["taggable_id", 62696], ["taggable_type", "SongVersion"]]
=> [#<ActsAsTaggableOn::Tagging:0x00007fe3abdc92b8
  id: 966031,
  tag_id: 1,
  taggable_id: 62696,
  taggable_type: "SongVersion",
  tagger_id: nil,
  tagger_type: nil,
  context: "genres",
  created_at: Fri, 26 Jul 2019 10:27:00 PDT -07:00>,
 #<ActsAsTaggableOn::Tagging:0x00007fe3abdc8fe8
  id: 966030,
  tag_id: 21,
  taggable_id: 62696,
  taggable_type: "SongVersion",
  tagger_id: nil,
  tagger_type: nil,
  context: "genres",
  created_at: Fri, 26 Jul 2019 10:27:00 PDT -07:00>,
 #<ActsAsTaggableOn::Tagging:0x00007fe3abdc8d40

if I call sv.taggings, the records do all exist with the correct ids. The oddest part is the SQL seems correct but won't pull up the records. I posted in on stackoverflow here if you want to see the sql queries.

Is there a way to manually update the counter cache and tag list?