The Sequel extension adds the Sequel::Dataset#combine method, which returns object from database composed with childrens, parents or any object where exists any relationship. Now it is possible in one query!
MIT License
This extension adds the Sequel::Dataset#combine
method, which returns object from database composed with childrens, parents or any object where exists any relationship. Now it is possible in one query!
Add this line to your application's Gemfile:
gem 'sequel-combine'
And then execute:
$ bundle
Or install it yourself as:
$ gem install sequel-combine
The plugin needs to be initialized by the Sequel extension interface. The simplest way to configure plugin globally is adding this line to the initializer:
Sequel.extension :combine
or
Sequel::Database.extension :combine
But anyway I recommend reading more about Sequel extensions system.
Remember! Combined dataset it's still a dataset so methods can be chained!
Combining works only with Postgres adapter
dataset_first
.combine(many: { attribute: [dataset_second, p_key_dataset_second: :f_key_dataset_first] })
.to_a
dataset_first
, dataset_second
-> datasets which needs to be combinedmany
-> method used in combining. If relation is one-to-one recommended method is one
(which return object or nil), in any other case I recommend to using method many
(which return array of objects or empty array).attribute
-> attribute which will be an result of combinep_key_dataset_second: :f_key_dataset_first
-> relationship between tablesDB[:groups].columns
#=> [:id, :name]
DB[:users].columns
#=> [:id, :username, :email, :group_id]
DB[:groups].combine(many: { users: [DB[:users], id: :group_id] }).to_a
#=> [{:id=>1,
# :name=>"Football",
# :users=>
# [{
# :id=> 1,
# :username=> "leonardo",
# :email=> "[email protected]",
# :group_id=> 1,
# },
# {
# :id=> 2,
# :username=> "leonardo2",
# :email=> "[email protected]",
# :group_id=> 1,
# },
# ]
# }]
DB[:groups].columns
#=> [:id, :name]
DB[:users].columns
#=> [:id, :username, :email, :group_id]
DB[:users].combine(one: { group: [DB[:groups], group_id: :id] }).to_a
#=> [
# {
# :id=> 1,
# :username=> "leonardo",
# :email=> "[email protected]",
# :group=> { :id=> 1, :name=> "Football" },
# },
# {
# :id=> 2,
# :username=> "leonardo2",
# :email=> "[email protected]"
# :group=> { :id=> 1, :name=> "Football" },
# }
# ]
Also combining can be mixed and multiplied:
DB[:users].combine(
one: {
group: [DB[:groups], group_id: :id],
company: [DB[:companies], company_id: :id],
},
many: {
tasks: [DB[:tasks], id: :user_id],
roles: [DB[:roles], id: :user_id],
},
).to_a
It can go deeper and deeper...
DB[:projects].combine(
many: {
users: [
DB[:users].combine(one: { city: [DB[:cities], city_id: :id] }),
id: :project_id,
]
}
).to_a
DB[:geolocations].combine(one: { parent: [DB[:geolocations], path: :parent_path] }).to_a
Datasets used in combine might be of course chained with other Sequel::Dataset
methods.
DB[:groups]
.where(id: 1)
.select(:id, :name)
.order(:name)
.combine(
many: {
users: [
DB[:users]
.join(:groups)
.select(:id, :username, :group_id, Sequel.qualify("groups", "name")),
id: :group_id
]
}
).to_a
Tested on 2000 mocked records with children's or parents:
4 level of combine - 2,39 sec.
3 level - 1,12 sec.
2 level - 0,55 sec.
1 level - 0,22 sec.
self-combining (the situation from geolocation, tested on real geolocations database, around 23000 records) - 4 sec
git checkout -b my-new-feature
)git commit -am 'Add some feature'
)git push origin my-new-feature
)