Unable to query a model where 2 associations use the same model

[jwoertink]

Related: #1007

Say you have a model where it has 2 belongs_to and both of those associations are the same model (e.g. User), any query you do that requires both will only use 1 join and override any where clauses set.

class Interview < BaseModel
  table do
    belongs_to interviewer : User
    belongs_to interviewee : User
  end
end

# I want to query for all interviews where the Interviewer isn't available for hire, but the Interviewee is
InterviewQuery.new
      .where_interviewer(UserQuery.new.available_for_hire(false))
      .where_interviewee(UserQuery.new.available_for_hire(true))

In this case, we end up getting this SQL which is not what I want

SELECT interviews.*
FROM interviews
INNER JOIN users ON interviews.interviewer_id = users.id
WHERE users.available_for_hire = 'false'
  AND users.available_for_hire = 'true'

My initial thought is if we just always alias the joins to your association method name, then maybe it'll fix this. However, I think that may also be a breaking change for anyone using where(raw_query : String).

So where_interviewer would go from

INNER JOIN users ON interviews.interviewer_id = users.id

to

INNER JOIN users AS interviewers ON interviews.interviewer_id = interviewers.id

But then anyone that was doing where("users.something @> ?", ...) would now get runtime errors since they would need to change it to where("interviewers.something @> ?", ...).

Maybe we can use some annotation to turn on this change at compile time and give people time to swap over? I'm not sure how that would work. 🤔

[robacarp]

I think your idea of using the relation name as the table alias for the join is spot on, and would be the most natural too.

[jwoertink]

The PR #1012 now has a working example of how this can work.

Before doing anything "magical" under the hood, this can now be done manually like this

    InterviewQuery.new
      .join(Avram::Join::Inner.new(:interviews, :users, alias_to: :interviewers, primary_key: :interviewer_id, foreign_key: :id))
      .join(Avram::Join::Inner.new(:interviews, :users, alias_to: :interviewees, primary_key: :interviewee_id, foreign_key: :id))
      .where_interviewer(UserQuery.new("interviewers").available_for_hire(false), auto_inner_join: false)
      .where_interviewee(UserQuery.new("interviewees").available_for_hire(true), auto_inner_join: false)

The join part will be easy to do. where_whatever does auto inner joins by default, and we know what the association is at compile time here. However, the UserQuery.new("alias name") part is where it gets tricky. This query is built by you, so this query isn't known at compile time.

It's almost like I need something like

def where_whatever(some_query : BaseQuery)
  new_query = BaseQuery.new("known_alias")
  new_query.merge(some_query)
  # do rest of stuff
end

This is the part I'm foggy on and unsure of how to handle.

[akadusei]

avram/src/avram/query_builder.cr

Line 311 in 23aa237

if join_clause.to != table && @joins.none? { |join| join.to == join_clause.to }

That line looks like it will not allow you to add the same join table name twice.

However, the UserQuery.new("alias name") part is where it gets tricky. This query is built by you, so this query isn't known at compile time.

I haven't looked at your PR, but have you tried making the alias a method instead:

UserQuery.new.alias(:interviewers)