SqlAlchemy outerjoin query problem filtering

Question

I'm using sqlalchemy, and I have problem with this specific query.

I have data_template, devices_data, and device. Each device have value for each data in data_template. Those values are stored in devices_data. I want to list data_template for one device with values that this device has. If there is no value for some data_template, show None.

It has something to do with outerjoin. Here is my model:

from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class DataTemplate(Base):
    __tablename__ = 'data_template'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return f"<DataTemplate(name={self.name})>"


class Device(Base):
    __tablename__ = 'device'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return f"<Device(name={self.name})>"


class DeviceData(Base):
    __tablename__ = 'device_data'
    id = Column(Integer, primary_key=True)
    value = Column(Integer, nullable=False)
    data_name_id = Column(Integer, ForeignKey(DataTemplate.id), nullable=False)
    device_id = Column(Integer, ForeignKey(Device.id), nullable=False)

    data_template = relationship('DataTemplate', backref='device_data')
    device = relationship('Device', backref='device_data')

    def __repr__(self):
        return f"<DeviceData(device={self.device.name}, data_template={self.data_template.name}, value={self.value})>"


engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()

dev1 = Device(name='Receiver')
dev2 = Device(name='TV')


dat_temp1 = DataTemplate(name="height")
dat_temp2 = DataTemplate(name="width")
dat_temp3 = DataTemplate(name="length")

session.add_all([dev1, dev2, dat_temp1, dat_temp2, dat_temp3])

dd1 = DeviceData(value=100, data_template=dat_temp1, device=dev1)
dd2 = DeviceData(value=50, data_template=dat_temp2, device=dev1)
dd3 = DeviceData(value=200, data_template=dat_temp1, device=dev2)
dd4 = DeviceData(value=40, data_template=dat_temp2, device=dev2)
dd5 = DeviceData(value=30, data_template=dat_temp3, device=dev2)

session.add_all([dd1, dd2, dd3, dd4, dd5])

s = session.query(DataTemplate, DeviceData).outerjoin(DeviceData).filter(DeviceData.device==dev1)
for x in s:
    print(x)

with this outerjoin I'm getting:

(<DataTemplate(name=height)>, <DeviceData(device=Receiver, data_template=height, value=100)>)
(<DataTemplate(name=width)>, <DeviceData(device=Receiver, data_template=width, value=50)>)

and is equal to:

SELECT "d"."id", "val"."id"
FROM "DataTemplate" "d"
  LEFT JOIN "DeviceData" "val"
    ON "d"."id" = "val"."data_name_id"
WHERE "val"."device_id" = 1

but I want to get:

 (<DataTemplate(name=height)>, <DeviceData(device=Receiver, data_template=height, value=100)>)
 (<DataTemplate(name=width)>, <DeviceData(device=Receiver, data_template=width, value=50)>) 
 (<DataTemplate(name=length)>, None)

and that query should be:

SELECT "d"."id", "val"."id"
FROM "DataTemplate" "d"
  LEFT JOIN "DeviceData" "val"
    ON "d"."id" = "val"."data_name_id" AND "val"."device_id" = 1

how do I write this specific query?

`Query.outerjoin()` accepts an explicit ON clause as the second positional argument: https://stackoverflow.com/questions/55257893/how-left-outer-join-in-sqlalchemy, https://stackoverflow.com/questions/55618773/sqlalchemy-filter-children-in-query-but-not-parent — Ilja Everilä, Apr 27 '20 at 15:21

SqlAlchemy outerjoin query problem filtering

0 Answers0