SequelCombine
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!
Installation
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 :combineor
Sequel::Database.extension :combineBut anyway I recommend reading more about Sequel extensions system.
Usage
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 combined -
many-> method used in combining. If relation is one-to-one recommended method isone(which return object or nil), in any other case I recommend to using methodmany(which return array of objects or empty array). -
attribute-> attribute which will be an result of combine -
p_key_dataset_second: :f_key_dataset_first-> relationship between tables
Usage examples
Combining many
DB[: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=> "leonardo@fakemail.com",
# :group_id=> 1,
# },
# {
# :id=> 2,
# :username=> "leonardo2",
# :email=> "leonardo2@fakemail.com",
# :group_id=> 1,
# },
# ]
# }]Combining one
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=> "leonardo@fakemail.com",
# :group=> { :id=> 1, :name=> "Football" },
# },
# {
# :id=> 2,
# :username=> "leonardo2",
# :email=> "leonardo2@fakemail.com"
# :group=> { :id=> 1, :name=> "Football" },
# }
# ]Combining one and many
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_aCombining inside combine
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_aSelf-combining and combining not by foreign_key
DB[:geolocations].combine(one: { parent: [DB[:geolocations], path: :parent_path] }).to_aCombining more complex datasets
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_aBenchmark
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
Use cases
- API directly in Postgresql
- Exporting tree of objects
- deep clone in Postgresql - very extreme case, but it's probably the most performance effective way of doing this operation
- more, more, more...
Contributing
- Fork it
- Create your feature branch (
git checkout -b my-new-feature) - Commit your changes (
git commit -am 'Add some feature') - Push to the branch (
git push origin my-new-feature) - Create new Pull Request