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