I'm pretty new to Sequel and I'm scratching my head trying to figure out how to get Sequel's API to generate the following trivial SQL:

          select f.* from first f
          join second s on f.second_id = s.id
          where s.deactivated = false

The best I could come up with is:

      First.join(:second, deactivated: false, id: :second_id)
        .paged_each do |first|
          # magic happens here
        end

But that does select * not select first.* and as a result gets confused about what id to order by for paging and throws PG::AmbiguousColumn: ERROR: ORDER BY "id" is ambiguous

This is using Sequel 5.9.0 and Postres 10.

Solution

Thanks to @engineersmnky's suggestion about qualify and some further reading here's the approach I ended up using.

      First.join(:second, id: :second_id, deactivated: false)
        .qualify
        .stream.each do |first|
          # magic happens here
        end

The qualify method call resolves the ambiguity (and ensures only first table gets returned.

I also added sequel_pg gem so I can use stream.each rather than paged_each. This has a better performance but also removes the need for the order by id that was causing me grief initially.

Disclaimer: I have never actually used sequel

There appears to be a method Sequel::Dataset#qualify that will do exactly what you are asking and should result in:

select first.* from first 
  join second on first.second_id = second.id
  where second.deactivated = false

I think the implementation would look like:

First.join(:second, id: :second_id)
    .where(Sequel[:second][:deactivated] => false) 
    #OR .where("second.deactivated": false)
    #OR .where{[[second[:deactivated],false]]}
    .qualify
    .paged_each do |first|
      # magic happens here
    end

Now if First and Second are properly associated Sequel::Models it appears the join condition can be inferred through association_join See Here from the docs e.g.

First.association_join(:second)
    .where(Sequel[:second][:deactivated] => false)
    .qualify
    .paged_each do |first|
      # magic happens here
    end