Executing JOINs
Implicit Joining
Mapped classes of related Microsoft Dynamics 365 objects are joined implicitly if there is a singular foreign key relationship. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String, Integer, DateTime, ForeignKey from sqlalchemy.orm import sessionmaker, relationship Base = declarative_base() class Contact(Base): __tablename__ = "Contact" Id = Column(Integer, primary_key=True) Name = Column(String) Email = Column(String) BirthDate = Column(DateTime) AccountId = Column(String, ForeignKey("Account.Id")) Account_Link = relationship("Account", back_populates="Contact_Link") class Account(Base): __tablename__ = "Account" Id = Column(String, primary_key=True) Name = Column(String) BillingCity = Column(String) NumberOfEmployees = Column(Integer) Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")
Once the relationship is established, the tables are queried simultaneously using the session's "query()" method, as below:
rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId) for Ac, Ct in rs: print("AccountId: ", Ac.Id) print("AccountName: ", Ac.Name) print("ContactId: ", Ct.Id) print("ContactName: ", Ct.Name)
Other Join Forms
There may come situations where mapped classes have either no foreign keys or multiple foreign keys. In such situations, different forms of the JOIN query may be needed to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:
- Explicit condition (necessary if there are no foreign keys in your mapped classes):
rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId) for Ac, Ct in rs:
- Left-to-Right Relationship:
rs = session.query(Account, Contact).join(Account.Contact_Link) for Ac, Ct in rs:
- Left-to-Right Relationship with explicit target:
rs = session.query(Account, Contact).join(Contact, Account.Contact_Link) for Ac, Ct in rs:
- String form of a Left-to-Right Relationship:
rs = session.query(Account, Contact).join("Contact_Link") for Ac, Ct in rs: