Michael R. Cook Ruby and Golang Developer

Category: Sequel

Sequel ORM: scope for associated records

Post Image

I’m currently experimenting with building a web application using Sinatra and the Sequel ORM, and as is often required in database driven applications, I need to pull a bunch of records yet exclude those that don’t have any associated records in a one_to_many relationship (has_many in Rails parlance).

Imagine we are building a blog platform where we can have Post’s that get assigned to a Category (many_to_one). When listing our available categories we don’t want to be showing any that haven’t any posts assigned. I wasn’t able to gleam anything specific about doing this from the Sequel documentation, so after a prolonged hair-pulling session I came to the following solution.

First, here’s the basic outline of our models;

class Post < Sequel::Model
  many_to_one :category
end

class Category < Sequel::Model
  one_to_many :posts
  
  dataset_module do
    def with_posts
      where(id: Post.select(:category_id))
    end
  end
end

As you can see, we are using a dataset_module to set the scope for our query, allowing us to call .with_posts wherever we need a listing of categories. The where(id: Post.select(:category_id)) tries to match any Post that has our category.id. Using our new scope we can now set our controller like so;

get "/categories" do
  @categories = Category.with_posts.order(:name)
  erb :categories
end

In Rails we have the option to set a counter cache on the Category model; posts_count, which let’s us filter by that. Sequel has the sequel_postgresql_triggers plugin, which let’s you do the same (using pgt_counter_cache) if you are using PostgreSQL, although I haven’t looked into that just yet.

About Me

Hi, my name is Michael and this is my personal blog. Here I’ll be posting my coding thoughts and experiments, specifically in regards to building websites in Ruby (Rails, Roda, Sinatra, etc). This site is powered by Thunderaxe, a blogging platform I’m building using the Roda Ruby framework, which I hope to be open sourcing in the near future.