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.

Hoofbeat

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

Renown

Answer 2

Disclosure

Not an Sequel expert. But I do use it.

I think where statements that come after joins are NOT virtual rows. You'd have to qualify them in some way - here's one way - and there are probably other ways too:

Solution

DB[:F].join(:S, id: :second_id).where(Sequel[:S][:deactivated] => false).sql

SQL produced:

SELECT * FROM F INNER JOIN S ON (S.id = F.second_id) WHERE (S.deactivated IS FALSE)

To Check:

1. Clone the Sequel Gem: https://github.com/jeremyevans/sequel
2. Run the following command in the root directory of the gem:

bin/sequel -c "puts DB[:F].join(:S, id: :second_id).where(Sequel[:S][:deactivated] => false).sql"

Nunhood