MaxLap
🐛 Composite Primary Keys (Rails 7.1 and above) not supported
I've included a test case that returns an error indicating the composite primary keys are not respected
BugTest#test_associations:
ActiveRecord::StatementInvalid: ...: no such column: kids.["version_id", "organisation_id", "father_id"]
due to this generated query :
❌
SELECT COUNT(*) FROM "fathers"
WHERE (
EXISTS (
SELECT 1 FROM "kids"
WHERE "kids"."[""version_id"", ""organisation_id"", ""father_id""]" = "fathers"."[""version_id"", ""organisation_id"", ""local_id""]"
AND "kids"."version_id" = 1
AND "kids"."organisation_id" = 9234
AND "kids"."medals" = 11
)
)
Expectation is it would generate something akin to
✅
SELECT COUNT(*) FROM "fathers"
WHERE (
EXISTS (
SELECT 1 FROM "kids"
WHERE
"kids"."version_id" = "fathers"."version_id"
AND "kids"."organisation_id" = "fathers"."organisation_id"
AND "kids"."father_id" = "fathers"."local_id"
AND "kids"."version_id" = 1
AND "kids"."organisation_id" = 9234
AND "kids"."medals" = 11
)
)
I've left some other (passing) tests in (but commented out), in case you want to get an idea of the kinds of compose-primary-key-based queries activerecord generates
# frozen_string_literal: true
require "bundler/inline"
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", '~> 7.1.3'
# If you want to test against edge Rails replace the previous line with this:
# gem "rails", github: "rails/rails", branch: "main"
gem "sqlite3", '~> 1.4'
gem 'activerecord_where_assoc', '~> 1.1.5'
end
require "active_record"
require "minitest/autorun"
require "logger"
# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Schema.define do
create_table :versions, force: true do |t|
t.string :name
t.string :descriptor
end
create_table :organisations, primary_key: [:version_id, :local_id], force: true do |t|
t.bigint :version_id
t.bigint :local_id
t.string :name
end
create_table :fathers, primary_key: [:version_id, :organisation_id, :local_id], force: true do |t|
t.bigint :version_id
t.bigint :organisation_id
t.bigint :local_id
t.string :name
end
create_table :mothers, primary_key: [:version_id, :organisation_id, :local_id], force: true do |t|
t.bigint :version_id
t.bigint :organisation_id
t.bigint :local_id
t.string :name
end
create_table :kids, primary_key: [:version_id, :organisation_id, :local_id], force: true do |t|
t.bigint :version_id
t.bigint :organisation_id
t.bigint :local_id
t.string :name
t.integer :medals
t.bigint :father_id
t.bigint :mother_id
end
end
class Version < ActiveRecord::Base
has_many :organisations
end
class Organisation < ActiveRecord::Base
self.primary_key = [:version_id, :local_id]
belongs_to :version
end
class Mother < ActiveRecord::Base
self.primary_key = [:version_id, :organisation_id, :local_id]
belongs_to :version
belongs_to :organisation,
query_constraints: [:version_id, :organisation_id]
has_many :kids,
query_constraints: [:version_id, :organisation_id, :mother_id]
has_many :entanglements, through: :kids, source: :father
end
class Father < ActiveRecord::Base
self.primary_key = [:version_id, :organisation_id, :local_id]
belongs_to :version
belongs_to :organisation,
query_constraints: [:version_id, :organisation_id]
has_many :kids,
query_constraints: [:version_id, :organisation_id, :father_id]
has_many :entanglements, through: :kids, source: :mother
end
class Kid < ActiveRecord::Base
self.primary_key = [:version_id, :organisation_id, :local_id]
belongs_to :version
belongs_to :organisation,
query_constraints: [:version_id, :organisation_id]
belongs_to :mother,
query_constraints: [:version_id, :organisation_id, :mother_id]
belongs_to :father,
query_constraints: [:version_id, :organisation_id, :father_id]
end
class BugTest < Minitest::Test
def test_associations
version = Version.create!(name: 'K-Swiss', descriptor: 'Rcudgel Xmin for the foreseeable')
organisation = Organisation.create!(name: 'Effaclar', local_id: 9234, version: version)
base_version_organisation_details = {
version: version,
organisation: organisation
}
mother =
Mother.create!(base_version_organisation_details.merge(
local_id: 98,
name: 'Mariana'
))
father =
Father.create!(base_version_organisation_details.merge(
local_id: 117,
name: 'Carlito'
))
father_2 = Father.create!(base_version_organisation_details.merge(
local_id: 1982,
name: 'Abdel-aziz'
))
### Can pass mother/father objects and let Rails figure it out
kid_1 = Kid.create!(base_version_organisation_details.merge(
local_id: 1,
name: 'Jim',
mother: mother,
father: father,
medals: 5
))
### Can pass specific mother/father Local IDs
kid_2 = Kid.create!(base_version_organisation_details.merge(
local_id: 2,
name: 'Jesse',
mother_id: mother.local_id,
father_id: father.local_id,
medals: 9
))
kid_3 = Kid.create!(base_version_organisation_details.merge(
local_id: 29,
name: 'Sinbad',
mother: mother,
father: father_2,
medals: 11
))
# puts "assert_equal 1, Father.count"
# assert_equal 2, Father.count
# puts "assert_equal 1, Mother.count"
# assert_equal 1, Mother.count
# puts "assert_equal 2, Kid.count"
# assert_equal 3, Kid.count
# puts "assert_equal 2, father.kids.count"
# assert_equal 2, father.kids.count
# puts "assert_equal 2, mother.kids.count"
# assert_equal 3, mother.kids.count
# puts "assert_equal father.kids.to_a, mother.kids.to_a "
# assert_equal kid_1.father, father
# puts "assert_equal 25, mother.kids.sum(:medals)"
# assert_equal 25, mother.kids.sum(:medals)
# puts "assert_equal kid_1.father.entanglements.count, kid_2.father.entanglements.count"
# assert_equal kid_1.father.entanglements.count, kid_2.father.entanglements.count
# puts "assert_equal kid_2.mother.entanglements.count, kid_1.mother.entanglements.count"
# assert_equal kid_2.mother.entanglements.count, kid_1.mother.entanglements.count
# puts "assert_equal 3, kid_3.mother.entanglements.count"
# assert_equal 3, kid_3.mother.entanglements.count
# puts "assert_equal 1, kid_3.father.entanglements.count"
# assert_equal 1, kid_3.father.entanglements.count
# puts "assert_equal 2, kid_2.father.entanglements.count"
# assert_equal 2, kid_2.father.entanglements.count
assert_equal 1, Father.where_assoc_exists(:kids, {
version_id: version.id,
organisation_id: organisation.local_id,
medals: kid_3.medals
}).count
end
end
I'll have a dig through the code.
Looks like there would need to be some changes to
-
CoreLogic.wrapper_and_join_constraints, with a an expansion of this to support multiple conditions -
ActiveRecordCompat.join_keys
at least
Thanks for the thorough reporting!
The main case is likely happening in CoreLogic.wrapper_and_join_constraints. The join_keys's values are arrays instead of symbols, so there would need to be iteration for the constraints = table[key].eq(foreign_table[foreign_key]).
There are other places referring to primary_key and foreign_key in CoreLogic which also likely need to be handled. I'd suggest making a failing test for each case first, then altering the code to make sure the test case does hit the location as the code is quite complex due to all of different cases.
Good luck and let me know if I can be of help
Hum, seems I didn't see your 2nd message while I was looking around. I guess we are agreeing :)
I don't think that ActiveRecordCompat.join_keys needs to be changed. I would just add some checks to handle when the keys are arrays.
I'll make a fork to get support going for the basic case in the test above, then I'll consider making composite-primary-key variants of each of your existing test cases to make sure the more weird and wonderful usages are still covered.
@MaxLap I have a candidate fix for you to review at #18 The gem's test setup is a bit more bespoke than I can add to confidently without some guidance, I could use some pointers / an exemplar for a composite-primary-key-like model.
Sorry, I forgot about the test system... It's been a while.
Lots of meta programming because I wanted most model & association to have their own conditions that couldn't overlap (each require an identifier to be a multiple of a different prime). So accessing a S1 via an association means that the it must be a multiple of 7 (just an example prime) and of 31 (another random prime) from the S1 model and the association itself. So when creating the model, the code must set the correct value.
So when creating objects, create_default! and create_assoc! are used which populate the column with a number that matches the conditions for the specified associations.
Definitely overly complex and I wouldn't do it again if I was redoing this all.
I'll take care of the task and the testing, I don't want to impose this burden on you and I think it's something worth supporting. I'm travelling tomorrow for two weeks, so if I program while travelling, I'll look at this, otherwise it will be when I come back. If I forget, ping me in september.