Python SQLAlchemy find unique record from three tables

Question

I have three tables admin, doctor, manager and they have no values that are common in them and each of them has an email column but since emails are unique there are no common values.

I want to write a query in SQLAlchemy which joins three tables and then I can filter the three virtual email column based on the email provided by the user.

Table admin

id (unique)
email (unique)
name (unique)

Table doctor

id (unique)
email (unique)
doctorName (unique)

Table manager

id (unique)
email (unique)
managerName (unique)

def get_role_by_user_email(email):  
    query = session.query(admin,
                   doctor,
                   manager
                   )\
            .filter(admin.email == email)\
            .filter(doctor.email == email)\
            .filter(manager.email == email)\
            .first()\

    I am hoping the query will result in a unique row from three tables.

JOIN is not working as there are no common records in terms of ids and emails between these three tables.

In SQL terms this is what I am trying to achieve

results = admin table where admin.email == user_provided_email or doctor table where doctor.email == user_provided_email or manager table where manager.email == user_provided_email

results object will contain only one row.

Thanks

If you managed to query it like this, how would you return which role you found the user in? Why don't you just place 3 conditional statements. First check if you can find in admin table, if you can, return role='admin'; elif check in next table, etc. — Andrew Clark, May 28 '21 at 20:37
You probably want to use a `union`, as described in [this answer](https://stackoverflow.com/a/24827766/5320906). However it may not be possible to retrieve the model of the matched record. Or you could avoid the problem by having a single `Person` table with a foreign key to a `Role` table. — snakecharmerb, May 29 '21 at 07:26

score 0 · Answer 1 · answered May 28 '21 at 20:43

You could build a dictionary, using email as the key and the role as the value.

email_role_dictionary = {}

query_objects = session.query(admin).all
for object in query_objects:
    email_role_dictionary[object.email] = 'admin'

Do the above for all 3 tables, into the same dictionary. and then access the desired role by looking in the dictionary for the email address key.

Python SQLAlchemy find unique record from three tables

1 Answers1