Using PaperTrail logs for has_and_belongs_to_many relationships

Using the Gem PaperTrail (https://github.com/airblade/paper_trail) to log has_and_belongs_to_many relationships can sometimes present challenges. This article aims to assist you in navigating these issues. Refer to the section on associations here to create associations for logging purposes.

Let's assume we have two models: User and House.

class User < ActiveRecord::Base
  has_paper_trail

  has_and_belongs_to_many :houses
end

class House < ActiveRecord::Base
  has_paper_trail

  has_and_belongs_to_many :users
end

create_table "houses", force: :cascade do |t|
  t.string   "name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "users", force: :cascade do |t|
  t.string   "name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

PaperTrail does not create versions for has_and_belongs_to_many relationships; therefore, we need to convert them into has_many relationships. We also need to add has_paper_trail to the join model (houses_users) to initiate logging properly.

class User < ActiveRecord::Base
  has_paper_trail

  has_many :houses_users
  has_many :houses, through: :houses_users
end

class House < ActiveRecord::Base
  has_paper_trail

  has_many :houses_users
  has_many :users, through: :houses_users
end

class HousesUser < ActiveRecord::Base
  has_paper_trail

  belongs_to :user
  belongs_to :house
end

Now, let's try assigning the first User an association with a House element in the console.

irb(main):011:0> User.first.house_ids = [3]

You will see that a version of the HousesUser model is created.

SQL (0.1ms)  INSERT INTO "versions" ("event", "created_at", "item_id", "item_type") VALUES (?, ?, ?, ?)  [["event", "create"], ["created_at", "2016-01-08 10:34:03.595991"], ["item_id", 10], ["item_type", "HousesUser"]]

Also, the version_association is created simultaneously.

SQL (0.1ms)  INSERT INTO "version_associations" ("version_id", "foreign_key_name", "foreign_key_id")
VALUES (?, ?, ?)  [["version_id", 25], ["foreign_key_name", "user_id"], ["foreign_key_id", 1]]
SQL (0.0ms)  INSERT INTO "version_associations" ("version_id", "foreign_key_name", "foreign_key_id") VALUES (?, ?, ?)  [["version_id", 25], ["foreign_key_name", "house_id"], ["foreign_key_id", 3]]
(137.7ms)  commit transaction
=> [3]

A version of the HousesUser model is created with the event: "create" if a new relationship is added, and "destroy" if a relationship is removed. In the PaperTrail::VersionAssociation record, you'll find entries like:

<PaperTrail::VersionAssociation id: 13, version_id: 25, foreign_key_name: "user_id", foreign_key_id: 1>
<PaperTrail::VersionAssociation id: 14, version_id: 25, foreign_key_name: "house_id", foreign_key_id: 3>

The version_id: refers to the ID of the version created for the HousesUser model. The foreign_key_name: comes from the foreign keys in the HousesUser model, which reference both the House and User models. Consequently, two records of PaperTrail::VersionAssociation are made to document these changes. The foreign_key_id: represents the value of the reference attribute in the HousesUser model record.

From this, we can write two ways to create a scope that retrieves the versions of HousesUser each time a relation between a User and a House is added:

PaperTrail::Version.where(item_type: "HousesUser").where_object user_id: 1

This method utilizes one of the built-in scopes offered by the gem, specifically where_object or where_object_changes. However, these scopes perform string comparison on the data, which can occasionally yield inaccurate results, although not very often.

"SELECT `versions`.* FROM `versions` WHERE `versions`.`item_type` = 'HousesUser' AND (`versions`.`object` LIKE '%\\nuser_id: 1\\n%')"

The segment ("versions"."object" LIKE "%\\nuser_id: 1\\n%") could potentially result in erroneous queries. In some cases, if a user possesses a string attribute with a value similar to what's being searched for, that record may also get pulled in during the querying process.

To accurately search using the PaperTrail::VersionAssociation table, you can write:

PaperTrail::Version.where(item_type: "HousesUser").joins(:version_associations).where(version_associations: {foreign_key_name: "user_id", foreign_key_id: 1})

From this query, you can identify association changes and log them accordingly. Thank you, and I hope this article proves helpful in your work!