Finding 2nd Order Relations: SQLAlchemy throws: "Please use the .select_from() method to establish an explicit left side"

user_1715544 0
Viewed 1770

I have a User model, a Contact model, and a Group model. I'm looking to find all of the 2nd-order User's Groups given a particular user in a single query. That is, I'd like to:

  • Use all contacts of a particular user...
  • ... to get all the users who are also a contact of the given user, and use that to...
  • ... get all groups of those (2nd-order) users

Right now I've got something like this (where user.id is the particular user whose contacts-of-contacts I'd like to find):

from sqlalchemy.orm import aliased

SecondOrderUser = aliased(User)

# This returns the phone number of all contacts who're a contact of the particular user
subquery = User.query \
    .join(Contact, Contact.user_id == User.id) \
    .filter(User.id == user.id) \
    .with_entities(Contact.contact_phone) \
    .subquery()

# This filters all users by the phone numbers in the above query, gets their contacts, and gets the group
# IDs of those contacts who are themselves users
contacts = User.query \
    .filter(User.phone.in_(subquery)) \
    .join(UserContact, UserContact.user_id == User.id) \
    .join(SecondOrderUser, SecondOrderUser.phone == UserContact.phone) \
    .join(Group, Group.user_id == SecondOrderUser.id) \
    .with_entities(Group.id) \
    .all()

The only thing that Contact and User share (to link them together—that is, to find contacts that are themselves users) is a common phone number. I [think I] could also do it with four join statements and aliases, but this gives me the same error. Namely:

sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from,
there are multiple FROMS which can join to this entity. Please use the .select_from()
method to establish an explicit left side, as well as providing an explicit ON clause
if not present already to help resolve the ambiguity.

What am I doing incorrectly here? Where/how to join feels clear to me, which indicates that I'm totally missing something.

flask-sqlalchemy sqlalchemy sql python
1 Answers

The problem here was two-fold. We've got to alias a table every time we use it (not just the 2nd time onward) and, when using with_entities, we've got to use all columns that we compare on—even if we don't intend on using their data in the end.

My final code looked something like this:

from sqlalchemy.orm import aliased

User1 = aliased(User)
User2 = aliased(User)
User3 = aliased(User)
Contact1 = aliased(Contact)
Contact2 = aliased(Contact)

contacts = User1.query \
    .join(Contact1, Contact1.user_id == User1.id) \
    .join(User2, User2.phone == Contact1.phone) \
    .join(Contact2, Contact2.user_id == User2.id) \
    .join(User3, User3.phone == Contact2.phone) \
    .join(Group, Group.user_id == User3.id) \
    .with_entities(
         Contact1.phone,
         Contact1.user_id,
         Contact2.phone,
         Contact2.user_id,
         Group.id
         Group.user_id,
         User1.id,
         User2.id,
         User2.phone,
         User3.id,
         User3.phone,
     ) \
     .all()
user_17155440
Related