How to get data from other tables using Sequel

Question

I use Sequel and I have the following 2 models in my Sinatra app

# foo.rb
require 'sequel'

module Notes
  module Models
    class Foo < Sequel::Model
      plugin :json_serializer, naked: true
      plugin :validation_helpers

      one_to_many :bars

      def validate
        super

        validates_presence [:foo_name], message: "can't be empty"
      end
    end
  end
end


# bar.rb
require 'sequel'

module Notes
  module Models
    class Bar < Sequel::Model
      plugin :json_serializer, naked: true
      plugin :validation_helpers

      many_to_one :foo

      def validate
        super

        validates_presence [:bar_name], message: "can't be empty"
      end
    end
  end
end

There is a foo_id foreign key in the Bar table.

I have an API route where you can get all bars or just a specific bar if a parameter is passed in, looks something like:

app.get '/api/bars' do
  require_session
  bar_name = params[:bar_name]
  bar_model = Models::Bar

  if bar_name.nil?
    bar_model.all.to_json
  else
    bar_model.where(Sequel.ilike(:bar_name, '%' + bar_name + '%'))
      .all
      .to_json
  end
end

What I'd like to do but haven't figured out yet is how can I also get at least foo_name from the Foo table in the result as well, retrieved based on the foo_id that's in Bar table?

Even more, what if there are even longer association, say there's another foreign key, for example baz_id, in the Foo table that's linked to a Baz table, and in this same API, I also want to get all the info from the Foo and Baz tables based on the foreign key association in those respective tables.

Hope that makes sense and any help is greatly appreciated.

Answer 1

You can use the :include option of Sequel's to_json method to do this. You can specify this in the model when you set the json_serializer options, but you can also override those defaults when you call to_json on an instance.

So, on an individual instance of Bar, you could do:

Bar.first.to_json(include: {foo: {only: :foo_name}})

If you want to do it for a full list, as in your example, you would want to call it on the class. Note that if you call it on an array this won't work, so you must call it on the dataset before converting it to an array.

# all models 
Bar.to_json(include: {foo: {only: :foo_name}})

# a subset of models
Bar.where(Sequel.ilike(:bar_name, '%' + bar_name + '%'))
  .to_json(include: {foo: {only: :foo_name}})