CData Python Connector for Microsoft Dynamics 365

Build 22.0.8462

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:

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462