Sequel shorts: eager load association with limit

Eager loading (preloading) only a certain number of associated records is pretty hard in SQL. But Sequel has you covered, if you are using a compatible database (such as Postgres).

	class Commit < Sequel::Model
	end

	class Repository < Sequel::Model
	one_to_many :recent_commits, :class => 'Commit',
	eager_limit_strategy: true,
	order: Sequel.desc(:id),
	limit: 3
	end

	# This will return all repositories with the last 3 commits loaded for each:
	# Repository.eager(:recent_commits).all

	# Queries generated on Postgres:
	# SELECT * FROM "repositories"
	# SELECT * FROM (
	# SELECT *,
	# row_number() OVER (PARTITION BY "commits"."repository_id" ORDER BY "id" DESC) AS "x_sequel_row_number_x"
	# FROM "commits" WHERE ("commits"."repository_id" IN (1))
	# ) AS "t1" WHERE ("x_sequel_row_number_x" <= 3)

view raw

sequel_assoc_limit.rb

hosted with ❤ by GitHub

Sequel shorts: Give me a hash!

Want to just get an array of hashes from a query instead of full-blown model objects? Easy.

	Artist.select(:id, :name).naked.all
	# => [{:id=>1, :name=>"mrbrdo"}]

view raw

sequel_naked.rb

hosted with ❤ by GitHub

Sequel shorts: Eager load association with extra conditions

December 8, 2014December 8, 2014mrbrdo Uncategorized Tags: sequel Leave a comment

Artist has many Songs

Get artists and load their songs which were released after 1990.

	artists = Artist.eager(songs: ->(q) { q.where{year > 1990} }).all

	# example to illustrate the results
	artists.each do \|artist\|
	puts "Artist: #{artist.name}"
	puts "Songs since 1990: " + artist.songs.map(&:title).join(", ")
	end

view raw

sequel_eager.rb

hosted with ❤ by GitHub

Rails model with unique string field (Postgres)

January 9, 2014November 21, 2014mrbrdo Uncategorized 3 Comments

UPDATE: Using a Postgres UUID field is a great way if you don’t care about the format of the unique field. See http://blog.arkency.com/2014/10/how-to-start-using-uuid-in-activerecord-with-postgresql/

	# Add a unique index in your migrations like this:
	# add_index :table, :code, unique: true

	before_create :set_code
	around_save :save_with_unique_code

	def self.generate_code
	SecureRandom.hex(2).upcase
	end

	def set_code(force = false)
	self.code = self.class.generate_code if code.nil? \|\| force
	end

	def save_with_unique_code
	begin
	self.class.connection.execute("SAVEPOINT before_record_create;")
	yield
	# NOTE: be careful if you have other unique fields!
	rescue ActiveRecord::RecordNotUnique
	set_code(true)
	self.class.connection.execute("ROLLBACK TO SAVEPOINT before_record_create;")
	retry
	end
	end

view raw

model_unique_field.rb

hosted with ❤ by GitHub

Why you should stop using ActiveRecord and start using Sequel

October 15, 2013September 28, 2016mrbrdo Ruby, Ruby on Rails 15 Comments

Note: Please be aware that we are talking about Sequel and not Squeel, which is a different library on top of ActiveRecord.

At first glance of the documentation, Sequel might strike you as a fairly innocent and basic ORM. Don’t let that fool you! Sequel is very powerful and supports virtually everything ActiveRecord does, and much more.

Lets get a few things out of the way first. Sequel is based on the active record pattern, so you will find it very similar to ActiveRecord (the ORM) in many ways. It was not designed specifically with Rails in mind, and unfortunately does not integrate with Rails by itself – which I consider an unfortunate downside. However fear not as there is a sequel-rails gem which will take care of that, and is actively maintained.

Why?

I’ll explain a few differences that I find particularly nice and useful below, but here is a quick list of a few things I like about Sequel:

Plays nice with threads, unlike ActiveRecord which has problems I discuss at the end of the post.
Allows you to avoid SQL code fragments 99% of the time, if you want and without screwing with core Ruby classes.
Modular design with plugins and extensions.
Foreign key constraint support in migrations out of the box.
Left join support. Heh.
More powerful association preloading.

A lot of it just has to do with the general feel of the ORM though. It’s hard to put the finger on it but Sequel just seems to have a more solid foundation to me.

Documentation

First I have to mention that while the documentation of Sequel is good, the organisation can be a bit problematic when you are trying to figure out how to do something. If you can’t find a way to do what you want, you can try to find help on IRC (#sequel on FreeNode). If you already know ActiveRecord, here is a good overview of how to do things.

The cheat sheet is also a useful resource.

Querying

Let’s just get this out of the way first – querying in Sequel is immensely more powerful compared to ActiveRecord. You can do almost anything without writing any SQL.

The basic querying syntax is very similar to ActiveRecord. I should actually probably put that the other way around since I believe Sequel had this syntax even before Rails 3 came around. Keep in mind that querying is just one aspect of an ORM, and is not by any means the only reason I like Sequel.

The first thing to note is that where in Sequel has two aliases, filter and more importantly, find. This will probably be annoying for you while you are getting used to Sequel. The analogy to find in Sequel is to use square brackets.

	# AR
	User.find(id)
	# Sequel
	User[id]

view raw

gistfile1.rb

hosted with ❤ by GitHub

That’s a bit weird but when you get used to it, it doesn’t really matter. Anyway, the where syntax is in its core the same as in ActiveRecord.

	User.where("name = ?", "Tom").first
	User.where(name: "Tom").first

view raw

gistfile1.rb

hosted with ❤ by GitHub

However, you do get more power with Sequel. Note that the non-SQL-fragment syntax is completely optional and you don’t need to use it if you don’t like it. You can continue using the same “prepared statement” style or hashes as with ActiveRecord, if you wish.

	# AR & Sequel
	User.where(name: "Tom")
	User.where("name = :name", name: "Tom")
	User.where("name = ? OR email = ?", "Tom", "jack@jack.com")
	User.where("users.name = ?", "Tom")

	# AR-only
	User.where(users: { name: "Tom" })
	# Sequel-only
	User.where(users__name: "Tom")
	User.where(Sequel.lit('users.name') => "Tom")

	# Sequel alternative option for "name = ? OR email = ?"
	User.where(Sequel.expr(name: "Tom") \| Sequel.expr(email: "jack@jack.com"))
	# Sequel alternative option for "name = ? OR email != ?"
	User.where(Sequel.expr(name: "Tom") \| Sequel.~(email: "jack@jack.com"))

	# AR-only
	User.select("SUM(id)").first
	# Sequel-only
	User.select(Sequel.function(:sum, :id)).first
	User.select(Sequel.lit("SUM(id)")).first

	# AR-only (4.x)
	User.where.not(name: "Tom")
	# Sequel-only
	User.exclude(name: "Tom")

view raw

gistfile1.rb

hosted with ❤ by GitHub

You might say that it takes more code in certain cases, but at least you don’t need to write SQL fragments, which is just ugly. You can always do that though, if you want. I think the :users__name part is particularly nice and useful.

Sequel used to, by default, monkey-patch certain core Ruby classes like Symbol to add functionality for querying – similar to Squeel for ActiveRecord. However this was turned off in later versions and deprecated. I just wanted to mention this as it shows an example of a good design decision.

As if all that wasn’t enough, there is also a block version of where that works similarly to that of Squeel. You should be using the latter version with a block parameter, or you might encounter hard-to-debug problems if you have local variables with the same name as a field you are referencing in the block.

	User.where { (id > 5) & (login_count < 10) }
	User.where { \|q\| (q.id > 5) & (q.login_count < 10) }

view raw

gistfile1.rb

hosted with ❤ by GitHub

Subqueries

Same as in ActiveRecord, it seems. Although in ActiveRecord it’s not a widely known feature.

	# AR and Sequel – results in single query, not two
	Artist.where(id: Album.select(:artist_id))

	# SELECT artists.* FROM artits WHERE id IN (SELECT artist_id FROM albums)

view raw

gistfile1.rb

hosted with ❤ by GitHub

Pluck

You might be sad when you find out there is no pluck in Sequel. Well, fear not, it just has a more sensible name – select_map. Which is really what pluck does, it selects and then maps. However in Sequel select_map was never limited to a single column like it was in ActiveRecord until Rails 4. Additionally, Sequel has select_hash, which will obviously give you a hash of the selected columns. You can also use naked.

	User.select_map(:id)
	# => [2]
	User.select_map([:id, :email])
	# => [[2, "user@example.com"]]
	User.select_hash(:id, :email)
	# => {2=>"user@example.com"}
	User.select(:id, :email).naked.all
	# => [{:id=>2, :email=>"user@example.com"}]

view raw

sequel_pluck.rb

hosted with ❤ by GitHub

select_all

Sequel has a useful method called select_all, which will simply make the select part of the query select all columns of a certain table:

Artist.select_all(:artists) #=> SELECT artists.* FROM artists

Can be useful when doing joins.

Order

In Sequel, you have order, but you also have reverse_order. Just continuing the trend of avoiding SQL code fragments. But note that order in Sequel will override all previous orders. If you don’t want that you’ll have to use order_append or order_prepend. This is again a demonstration of good design compared to ActiveRecord, which has random rules if orders are appended or prepended (they just changed it spontaneously in Rails 4). In Sequel there is no confusion about that.

If you are appending orders you might want to know that you can also order descending like this: Article.order(Sequel.desc(:name)). It’s not possible to use a SQL code fragment, so if you are porting code from ActiveRecord, you’ll have to fix your orders.

Eager loading

For eager loading, again, Sequel gives control to you. While ActiveRecord will decide for you if it will eager load by adding columns to the SELECT of your SQL, or doing an additional query, Sequel lets you choose yourself, which also makes it clear which of the two ways is being used. If you want to do eager loading in the same query, use eager_graph, if you want to eager load with an additional query, use eager. Using eager is generally recommended unless you need to filter by the other table. It’s usually faster too (even though that might be counter-intuitive to you).

	Artist.eager_graph(:albums).all
	Artist.eager(:albums).all

view raw

gistfile1.rb

hosted with ❤ by GitHub

You can also provide additional options to eager_graph to control which kind of join is made and provide extra join conditions.

LEFT JOIN

Don’t you just hate it when you need to do a left join in ActiveRecord? That has to be one of the most annoying things ever. Not in Sequel, though.

	# verbose, flexible version (specify which columns to join on)
	Album.left_join(:artists, artist_id: :id)
	# association "automatic" version (automatically determines which column to join on based on association on the model)
	Album.association_left_join(:artists)

view raw

gistfile1.rb

hosted with ❤ by GitHub

Plugins and extensions

Sequel is more modular than ActiveRecord. Beyond the basic functionality, you can enable plugins and extensions for extra features. There are a lot of these that come with Sequel, and all that I mention later on are already there for you.

If you are using sequel-rails, I recommend you use the new config.sequel.after_connect configuration option in your application.rb like so:

	config.sequel.after_connect = proc do
	Sequel::Model.db.extension :pagination
	Sequel::Model.db.extension :pg_hstore

	Sequel::Model.plugin :active_model
	Sequel::Model.plugin :validation_helpers
	Sequel::Model.plugin :dirty
	Sequel::Model.plugin :association_proxies
	Sequel::Model.plugin :timestamps, update_on_create: true
	end

view raw

gistfile1.rb

hosted with ❤ by GitHub

Note that you can also enable plugins on a per-model basis if you want.

created_at and updated_at

Sequel doesn’t update created_at/updated_at by default. But you can easily enable it by using the timestamps plugin.

Sequel::Model.plugin :timestamps, update_on_create: true

Associations

You might find querying on associations is a little too basic in Sequel (associations return simple Arrays by default). That’s just because you need to enable the association_proxies plugin.

Sequel::Model.plugin :association_proxies

Postgres hstore and arrays

While hstore and arrays are the new cool of Rails 4, it’s nothing new in the Sequel world. If you need it, just use the pg_array and pg_hstore extensions. Again, just add the following to your config.sequel.after_connect:

Sequel::Model.db.extension :pg_array
Sequel::Model.db.extension :pg_hstore

Pagination

Pagination support is built in to Sequel. Simply use the pagination extension. Simple and effective!

Sequel shorts

You can also check out some cool tricks with Sequel right here on my blog.

Threading

I first started looking into Sequel when I had problems with ActiveRecord and threading. While this might not be of importance to you, it is nice to know that you are set up to use threads if you need to, without having to fear about your connection pool depleting randomly.

The advantage of Sequel in the threading area is that it takes a slightly different approach than ActiveRecord to connection pooling, but it makes all the difference. In ActiveRecord, you can use ActiveRecord::Base.connection_pool.with_connection in combination with a block to checkout a connection, do some queries and then check the connection back into the pool. This is fine – the problem is when you let ActiveRecord manage the pool for you. In that case, when you do a query, ActiveRecord will automatically checkout a connection, but when the connection is checked back in is not up to you anymore, and here is the potential problem, depending on what your threads do and how long they live. It also means that a thread is locking up a connection even when it’s not using it, which is kind of lame too. You can argue that you can just use ActiveRecord::Base.connection_pool.with_connection in such cases, but it’s not hard to miss it somewhere (perhaps where it’s not obvious that a DB query is being made), and then you’re screwed. It’s close to impossible to find out where you forgot to manage the connection pool by yourself.

In Sequel, however, a connection is only checked out at the moment it is needed (i.e. just before performing an SQL query), and then checked back in, so it is immediately available to other threads again, even if the current thread will need it in the future. This is all done in a thread-safe manner. Obviously this makes it so you can usually have a smaller connection pool when doing threading. It is worth to mention that this approach is slightly slower / has more overhead than the ActiveRecord approach, but I think the benefits outweigh this by a large margin. Also, it is possible to turn this feature off if you are not using threads very easily by setting Sequel.single_threaded to true. The biggest benefit here is that you need not worry about threads locking up connections when they don’t need them.

Conclusion

As you can see, Sequel is really powerful. It gives us many tools that are simply not available in ActiveRecord. We can also usually avoid writing any SQL code, even though it may cost us a little bit more Ruby code, but nothing to write home about. It’s a good library for the advanced user, as you can customise it to your needs much more than ActiveRecord.

Add to all these features a sane support for threading, it has definitely become my ORM of choice. What I covered here is just a small preview of what Sequel has to offer you – so start using it!

Manually preloading associations in Rails using custom scopes/SQL

September 25, 2013April 20, 2015mrbrdo Ruby on Rails 9 Comments

In rare cases, you may need to manually preload associations in Rails for performance reasons. For example, you might have a situation where you can fetch multiple associations from the database in one query, or you need to do something that can’t be accomplished with Rails’ built-in support for eager loading.

First, let’s look at some situations where you might not know that it can be done with Rails’ built-in functionality.

Preloading / eager loading only one record from an association

For example you might have a Person that has_many photos, but when listing these people you only want to display the first photo. This is the naive and wrong way:

	class Person < ActiveRecord::Base
	has_many :photos
	has_one :display_photo, class_name: "Photo"
	end

	# WARNING: This only appears to work properly!
	people = Person.all.includes(:display_photo)
	people.each { \|person\| puts person.display_photo.id }

view raw

gistfile1.rb

hosted with ❤ by GitHub

It will appear as if it’s working, but the problem is that Rails will still preload all photos for each Person, not only the first one. You might think that a limit condition on the association would help, but it cannot, since Rails uses a query like the following for preloading:

SELECT “photos”.* FROM “photos” WHERE “photos”.”person_id” IN (1,2,3,4,5)

So, if you would stick a LIMIT at the end of that, it would not be limiting on a per-person basis. Here is what we can do to preload only the first photo:

	class Person < ActiveRecord::Base
	has_many :photos
	has_one :display_photo, -> {
	self.select_values = ["DISTINCT ON(photos.person_id) photos.*"]
	order('photos.person_id')
	}, class_name: "Photo"
	end

	# Polymorphic association example
	class Person < ActiveRecord::Base
	has_many :photos, as: :photo_parent
	has_one :display_photo, -> {
	fields = "photos.photo_parent_id, photos.photo_parent_type"
	self.select_values = ["DISTINCT ON(#{fields}) photos.*"]
	order(fields)
	}, as: :photo_parent, class_name: "Photo"
	end

	people = Person.all.includes(:display_photo)
	people.each { \|person\| puts person.display_photo.id }

view raw

gistfile1.rb

hosted with ❤ by GitHub

We use PostgreSQL’s DISTINCT ON to only select one photo for each person_id. Because of this we also need to add person_id as the first column to order by (this is required by postgres, and in Rails 4 orders are prepended to existing ones so this will be the first one). You can order descending if you need it to be the last photo. If you need to use more specific conditions, you can use additional scopes, however note that Rails will ignore everything except for: where, select, references, and includes.

We need to set select_values directly because DISTINCT ON must appear before specifying which columns to select.

Also, if you want to use a scope that you have defined on Image, you might have to do a little hack to be able to use that scope:

	class Photo < ActiveRecord::Base
	scope :enabled, -> { where(photo: { enabled: true }) }
	end

	class Person < ActiveRecord::Base
	has_one :display_photo, -> {
	self.select_values = ["DISTINCT ON(photos.person_id) photos.*"]
	instance_exec(&Photo.method(:enabled)).order('photos.person_id')
	}, class_name: "Photo"
	end

view raw

gistfile1.rb

hosted with ❤ by GitHub

Note that the same limitations apply on the types of scopes that you can use (so no group scope for example).

Manually preloading

You can look into code in active_record/associations/preloader/ to see more on how Rails does preloading, but basically, you need to do this:

	# collection association e.g. has_many

	owners = People.all
	association_name = :photos

	owners.each do \|owner\|
	records = Array(whatever_you_want)

	association = owner.association(association_name)
	association.loaded!
	association.target.concat(records)
	records.each { \|record\| association.set_inverse_instance(record) }
	end

	# singular association e.g. has_one

	owners = People.all
	association_name = :photos

	owners.each do \|owner\|
	record = whatever_you_want

	association = owner.association(association_name)
	association.target = record
	association.set_inverse_instance(record)
	end

view raw

gistfile1.rb

hosted with ❤ by GitHub

Gem

Some time later after writing this post, I came across a project where we needed to do a lot of pretty complex record preloading. I first created some preloading classes that basically added includes to the scope and later loaded stuff based on results. However soon there was some code duplication and it was also getting hard to understand because the preloaders were calling each other. There was a lot of boilerplate code so I needed some kind of abstraction to clean it up.

So I wrote a gem specifically for handling more complex preloading logic called datasource and another one for preloading specifically when using active_model_serializers called active_loaders (this one has documentation). These two gems are only intended for people who have a good understanding of SQL, especially the N+1 problem and how to efficiently fetch data in a single query. If you are a beginner the gems will not be of much help, even though there is some auto-magic.

Appendix

For my reference, here is another more complex way to do manual preloading by creating a custom preloader and calling it:

	has_one :display_gallery_item, as: :imageable, class_name: "GalleryItem"

	class ServiceCenterPreloader
	class GalleryItemPreloader < ActiveRecord::Associations::Preloader::HasOne
	def initialize(owners, reflection)
	super(GalleryItem, owners, reflection, ActiveRecord::Relation.new(nil, nil))
	end

	private
	def build_scope
	t = klass.table_name
	distinct = "#{t}.imageable_id, #{t}.imageable_type"
	scope = super
	scope.select_values = ["DISTINCT ON(#{distinct}) #{t}.*"]
	scope.order(distinct)
	end
	end

	def self.preload(service_centers)
	records = Array.wrap(service_centers.to_a).compact.uniq

	reflection = ServiceCenter.reflections[:display_gallery_item]
	GalleryItemPreloader.new(records, reflection).run
	end
	end

	service_centers = ServiceCenter.all.to_a
	ServiceCenterPreloader.preload(service_centers)

view raw

gistfile1.rb

hosted with ❤ by GitHub

Fix MacBook Pro Service Battery warning

May 31, 2013mrbrdo Uncategorized 7 Comments

Use coconutBattery to see the status of your battery. These steps are both for the old Macbooks and the new (unibody) Macbooks. Not sure about Air but it should work as long as you can open it.

Now do all these steps (I had to do all to make this warning go away).

1. Charge battery to 100% (charger green light).
1a. (probably not necessary) Discharge battery using Caffeine app, until the Macbook turns off, wait a few hours, then charge it back to 100%.
2. Shut down completely. Remove charger.
3. Open the bottom of the MacBook using a screwdriver. (You can leave it open until step 16)
4. Disconnect the battery.
5. Use a cotton swab with 70%+ alcohol and clean the contacts of the battery connector.
6. Hold the power button for 30 seconds, then press it a few times more.
7. Connect the charger (not battery) and wait a few seconds.
8. Reset SMC – press the (left side) Shift-Control-Option keys and the power button at the same time, hold for about 1 second, then release them all at the same time.
9. Reset NVRAM. (Steps 9a and 9b)
9a. Turn the Macbook on, then immediately press and hold the Command-Option-P-R keys before the gray screen appears.
9b. Hold the keys down until the computer restarts and you hear the startup sound for the second time. Then release the keys.
10. Let OSX boot or turn the Macbook on if it’s not on.
10. Login.
11. Now connect the battery while the Macbook is still on – this step was probably most important for me.
12. The battery indicator in OSX should show that the battery is now connected and at 99% (or 100%), and there should be no service battery anymore.
13. Wait a few minutes.
14. Shut down the Macbook completely.
15. Disconnect the charger.
16. Close back the Macbook case and screw it back on.
17. Turn on the Macbook and log in. The service battery warning should be gone.
18. Use Caffeine to drain the battery, do not turn off the Macbook until it shuts down itself.
19. Charge to 100%.

Enjoy.

Rails group count

May 30, 2013mrbrdo Ruby, Ruby on Rails Leave a comment

When you do group in Rails (SQL GROUP BY), count will return an ActiveSupport::OrderedHash. Sometimes you want the sum count (count of all the results, not by group). You can do count.keys.count, however this has serious performance issues if you have a lot of groups in the results.

I wrote an ActiveRecord scope that will override the count method to perform a more optimised count query (of course it will now return a FixNum instead of a Hash). You can use this in combination with Kaminari and get much more efficient calculation of total_pages and total_count.

Gemified (Ruby Gem): https://github.com/mrbrdo/active_record_group_count
Gist: https://gist.github.com/mrbrdo/5679455

Companion object in Ruby

February 28, 2013November 26, 2013mrbrdo Uncategorized Leave a comment

Scala has this concept of a companion object. One of the things the companion object does is let you define class methods. In Ruby we usually use one of these two approaches:

	class Klass
	def self.class_method ; end
	end

	class Klass
	class << self
	def class_method ; end
	end
	end

view raw

gistfile1.rb

hosted with ❤ by GitHub

We could have a similar construct to Scala. It seems Rails does something that clashes with defining the method ‘object’ in Kernel, so let’s call it companion instead.

	module Kernel
	def companion(klass, &block)
	klass.instance_eval(&block)
	# or: klass.singleton_class.class_eval(&block)
	end
	end

	class Cat
	end

	companion Cat do
	def describe
	puts "This is the Cat!"
	end
	end

	Cat.describe #=> This is the Cat!

view raw

gistfile1.rb

hosted with ❤ by GitHub

Maybe not the most useful thing, but it looks tidy and nice, especially if you have a lot of class methods.

Ruby with statement

February 27, 2013November 26, 2013mrbrdo Uncategorized 4 Comments

Well, Ruby doesn’t have a with statement (like Python or Pascal do). But Ruby is a cool language, so if you want to have it, you can have it.

	module Kernel
	def with(object, &block)
	object.instance_eval &block
	end
	end

	# Simple way to use it
	with "Ruby Rocks!" do
	puts self
	puts size
	end

	# More verbose way of using it
	with "Ruby" do \|s1\|
	with "Rocks!" do \|s2\|
	puts "#{s1} #{s2}"
	end
	end

view raw

core_ext_with.rb

hosted with ❤ by GitHub

Why not 🙂 Enjoy Ruby.

Mrbrdo's Weblog

Ruby on Rails tech blog

Sequel shorts: eager load association with limit

Sequel shorts: Give me a hash!

Sequel shorts: Eager load association with extra conditions

Rails model with unique string field (Postgres)

Why you should stop using ActiveRecord and start using Sequel

Why?

Documentation

Querying

Subqueries

Pluck

select_all

Order

Eager loading

LEFT JOIN

Plugins and extensions

created_at and updated_at

Associations

Postgres hstore and arrays

Pagination

Sequel shorts

Threading

Conclusion

Manually preloading associations in Rails using custom scopes/SQL

Preloading / eager loading only one record from an association

Manually preloading

Gem

Appendix

Fix MacBook Pro Service Battery warning

Rails group count

Companion object in Ruby

Ruby with statement