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 various coding thoughts and experiments; everything from writing blogs in Ruby, to Go tools and Z80 Assembly. This site is powered by Thunderaxe, a blogging platform I built using the Roda Ruby framework.