association_left_join doesn't involve association dataset

[vol1ura]

Hello. I have three models

class Carrier < Sequel::Model
  one_to_one :account, class: Account do |ds|
    ds.with_group('main_manager')
  end
end

class Partner < Sequel::Model
  one_to_one :account, class: Carrier do |ds|
    ds.with_group('main_manager')
  end
end

class Account < Sequel::Model
  many_to_one :carrier
  many_to_one :partner

  dataset_module do
    def with_group(group)
      where(some conditions)
    end
  end
end

But Carrier.association_left_join(account: :partner) give me

SELECT * FROM "carriers" 
LEFT JOIN "accounts" AS "account" ON ("account"."carrier_id" = "carriers"."id") 
LEFT JOIN "partners" AS "partner" ON ("partner"."id" = "account"."partner_id")

Why here is not involving with_group('main_manager') from association dataset definition?

[jeremyevans]

This is expected. association*join as documented, builds on the eager_graph support, which doesn't support association blocks. You need to use the :graph_conditions option to specify the conditions to use when graphing, or replace the association block with an appropriate :conditions option.

This is documented in eager_graph, but could be documented other places. Please let me know where you would like to see documentation regarding this, and I can add it.

[vol1ura]

Thank you for answer. There is little information about :graph_conditions in the documentation. I did not understand how to use it in my case.

I need to modify my model associations to get sql using Carrier.association_left_join(account: :partner):

SELECT "carriers".* FROM "carriers" 
LEFT JOIN (SELECT * FROM "accounts" WHERE ("accounts"."groups" @> ARRAY['main_manager']::enum_groups[])) AS "account" ON ("account"."carrier_id" = "carriers"."id") 
LEFT JOIN "partners" AS "partner" ON ("partner"."id" = "account"."partner_id")

[jeremyevans]

This works for me:

DB.extension :pg_array
Sequel.extension :pg_array_ops

GRAPH_BLOCK = proc do |j,lj,js|
  Sequel.pg_array_op(Sequel[j][:groups]).contains(Sequel.pg_array(['main_manager'], :enum_groups))
end

class Carrier < Sequel::Model
  one_to_one :account, class: :Account, graph_block: GRAPH_BLOCK do |ds|
    ds.with_group('main_manager')
  end
end

class Partner < Sequel::Model
  one_to_one :account, class: :Carrier, graph_block: GRAPH_BLOCK do |ds|
    ds.with_group('main_manager')
  end
end

class Account < Sequel::Model
  many_to_one :carrier
  many_to_one :partner

  dataset_module do
    def with_group(group)
      where(some conditions)
    end
  end
end

puts Carrier.association_left_join(account: :partner).sql
# SELECT * FROM "carriers"
# LEFT JOIN "accounts" AS "account"
#   ON (("account"."carrier_id" = "carriers"."id")
#      AND ("account"."groups" @> ARRAY['main_manager']::num_groups[]))
# LEFT JOIN "partners" AS "partner"
#   ON ("partner"."id" = "account"."partner_id")

Not exactly the same SQL as your subquery approach, which may matter considering you are using a left join. If you really want the subquery, it's best to use a different class:

DB.extension :pg_array
Sequel.extension :pg_array_ops

class Carrier < Sequel::Model
  one_to_one :account, class: :MainManagerAccount
end

class Partner < Sequel::Model
  one_to_one :account, class: :MainManagerAccount
end

class Account < Sequel::Model
  many_to_one :carrier
  many_to_one :partner
end

class MainManagerAccount < Account
  set_dataset dataset.where(Sequel.pg_array_op(:groups).contains(Sequel.pg_array(['main_manager'], :enum_groups)))
end

puts Carrier.association_left_join(account: :partner).sql
# SELECT * FROM "carriers"
# LEFT JOIN (
#   SELECT * FROM "accounts"
#   WHERE ("groups" @> ARRAY['main_manager']::enum_groups[])
# ) AS "account"
#   ON ("account"."carrier_id" = "carriers"."id")
# LEFT JOIN "partners" AS "partner"
#   ON ("partner"."id" = "account"."partner_id")

[vol1ura]

Thank you very much for the help!

[vol1ura]

One more question. Here the query in GRAPH_BLOCK will duplicate condition in association block. Is there any solution to not duplicate it in this approach?

DB.extension :pg_array
Sequel.extension :pg_array_ops

GRAPH_BLOCK = proc do |j, lj, js|
  Sequel.pg_array_op(Sequel[j][:groups]).contains(Sequel.pg_array(['main_manager'], :enum_groups))
end

class Carrier < Sequel::Model
  one_to_one :account, class: :Account, graph_block: GRAPH_BLOCK do |ds|
    ds.with_group('main_manager')
  end
end

[vol1ura]

@jeremyevans if use the association with conditions

one_to_many(
  :account,
  class: Account,
  conditions: { 
    Sequel.pg_array(:groups).contains(Sequel.pg_array(['main_manager'], :enum_groups)) => true
  }
)

then we have not very well constructed sql

WHERE ((("groups" @> ARRAY['main_manager']::enum_groups[]) IS TRUE) AND ("accounts"."carrier_id" = 1))

May be here is some better solution?

[jeremyevans]

I don't think there is currently a better solution. I suppose I could consider something like a :graph_use_association_block option that would use the association block when graphing.

[vol1ura]

Yes, that would be great. Intuitively I expect the same behavior for association*join.

Thanks for answer.

Yuriy