Sequel ORM logo 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.