CODEWITHSUNDEEP
pythonmysqldjangojoinsqlalchemy
Eldamir
Eldamir

Reputation: 10062

Django query with joins

I need to write a complex query, which retrieves a lot of data from a bunch of tables. Basically I need to find all instances of the models

where relationships intersect in a specific way. In SqlAlchemy, I would be able to do something like

for c, p, i in session.query(Customer, Payment, Invoice).\
        filter(User.id==Payment.customer_id).\
        filter(Invoice.id==Payment.invoice_id).\
        filter(Payment.date==...).\
        filter(Customer.some_property==...)
        all():
    # Do stuff ...

This would allow me to set several constraints and retrieve it all at once. In Django, I currently do something stupid like

customers = Customer.objects.filter(...)
payments = Payment.objects.filter(customer=customer)
invoices = Invoice.objects.filter(customer=customer, payment_set=payments)

Now, we already have three different queries (some details are left out to keep it simple). Could I reduce it to one? Well, I could have done something like

customers = Customer.objects.filter(...).prefetch_related(
    'payments', 'payments__invoices'
)

but now I have to traverse a crazy tree of data instead of having it all laid out neatly in rows, like with SqlAlchemy. Is there any way Django can do something like that? Or would I have to drop through to custom SQL directly?

Upvotes: 2

Views: 1555

Answers (1)

Eldamir
Eldamir

Reputation: 10062

After reading up on different solutions, I have decided to use SqlAlchemy on top of my Django models. Some people try to completely replace the Django ORM with SqlAlchemy, but this almost completely defeats the purpose of using Django, since most of the framework relies on the ORM.

Instead, I use SqlAlchemy simple for querying the tables defined by the Django ORM. I follow a recipe similar to this

# Setup sqlalchemy bindings
import sqlalchemy as s
from sqlalchemy.orm import sessionmaker
engine = s.create_engine('postgresql://<user>:<password>@<host>:<port>/<db_name>')
# Automatically read the database tables and create metadata
meta = s.MetaData()
meta.reflect(bind=engine)
Session = sessionmaker(bind=engine)
# Create a session, which can query the tables
session = Session()

# Build table instances without hardcoding tablenames
s_payment = meta.tables[models.Payment()._meta.db_table]
s_allocation = meta.tables[models.Allocation()._meta.db_table]
s_customer = meta.tables[models.Customer()._meta.db_table]
s_invoice = meta.tables[models.Invoice()._meta.db_table]

report = session.query(s_payment.c.amount, ...).all()

There is room for a few improvements on this recipe, e.g. it is not very elegant to create an empty instance of Django models in order to find their table name, however, with a few lines of code, I get the full flexibility of SqlAlchemy without compromising with the Django ORM layer. This means both can live happily alongside each other.

One caveat is that SqlAlchemy will not use the same connection as the Django ORM, which means that the view of things may not appear consistent if I use both approaches in the same context. This won't be a problem for me though, since I just want to read a bunch of data from the database.

Upvotes: 2

Related Questions