Search code examples
rubysinatrasequel

Sequel: exclude records that do not have associations


I have a Sinatra app using the Sequel ORM in which I'm trying to list only Categories that have one or more Posts.

So, if I have two categories in the database; "Apples" and "Oranges", and one Post assigned to "Apples", then when I list the current categories I only want the "Apples" category to be provided.

After much hair-pulling I finally managed to get it working with the following;

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

@categories = Category.with_posts

If there's a better way of doing this in Sequel please do let me know.


Solution

  • Try Jeremy's counter cache https://github.com/jeremyevans/sequel_postgresql_triggers

    # In your migration:
    pgt_counter_cache :categories, :id, :posts_count, :posts, :category_id
    
    # And in your code:
    categories = Category.exclude(posts_count: 0).all
    

    The documentation isn't very good so here are the arguments: https://github.com/jeremyevans/sequel_postgresql_triggers/blob/master/lib/sequel_postgresql_triggers.rb#L27