Sequel query with join and condition on the other table

Question

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.

Answer 1

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