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.
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.