On Saturday, July 14, 2018 at 12:54:35 AM UTC-7, Ramon Marco Navarro wrote:
>
> Hello,
>
> I have the following schema that was designed before we were aware that
> partitioning tables at the database server level is possible:
>
> people: id, partition_id
> profiles: id, person_id
> data_points_x: id, profile_id
> mappings_x: id, source_id, target_id
>
> class Person < Sequel::Model
> one_to_many :profiles
> end
>
> class Profile < Sequel::Model
> many_to_one :person
> one_to_many :data_points
> end
>
> class DataPoint < Sequel::Model
> many_to_one :profile
> one_through_one :source, join_table: mapping_x, left_key: target_id,
> right_key: source_id, class: self
> one_through_one :target, join_table: mapping_x, left_key: source_id,
> right_key: target_id, class: self
> end
>
> Is there a way for the associated table partly be based on a column? For
> example:
>
> Profile[1].data_points
> # SELECT people.partition_id FROM profiles JOIN people ON
> profiles.person_id = patients.id -- returns '3', for example
> # SELECT * FROM data_points_3 WHERE profile_id = 1; -- uses partition_id
> from Person model for data_points_3
>
Profile.one_to_many :data_points do |ds|
ds.from(:"data_points_#{person.partition_id}")
end
>
> DataPoint instances should also know from which partition they are to be
> able to correctly fetch its source and/or target:
>
> DataPoint[1].source
> # SELECT person.partition_id FROM data_points_3
> # JOIN profiles ON data_points_3.profile_id = profiles.id
> # JOIN patients ON patient_profiles.patient_id = patients.id
> # WHERE data_points.id = 1;
> # SELECT source_id FROM mappings_3 WHERE target_id = 1; -- assuming
> returns 2
> # SELECT * FROM data_points_3 WHERE id = 2;
>
Something like this may work:
DataPoint.one_through_one :source, join_table: mapping_x, left_key:
target_id, right_key: source_id, class: self do |ds|
partition_id = profile.person.partition_id
ds.from(:"data_points_#{partition_id}").clone(:join=>nil).join(:"mapping_#{partition_id}",
target_id: id, source_id: :id)
end
If not, you probably want to look at the sharding plugin and see how it is
implemented, and do something similar to table names that the sharding
plugin does for shards.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
