Hello everyone, i'am very new to SQLAlchemy and want to know - if the
problem described bellow is can be solved by SQLA ORM?
Here is the setup:
There are a Missions for car (travel from point A to point B, for example) -
model named *Mission*.
Mission cars(model named *Car*) are versioned by dates (see model bellow)
A car have a GPS device(model named *GPS*), which is used to send signals of
current position.
GPS devices also versioned by dates(see model bellow).
Attachment of GPS device to car(model named *CArGPS*) are also versioned
(see model bellow)
(Yes, it is very complicated and i can't change schema.)
Problem: setup relations that way, so i can get version of mission
car(by *Mission.date_start*) and get version of gps device attached to this
car
version (by *Mission.date_start*).
In terns if SQL it would be:
SELECT
mission.id AS mission_id,
mission.date_start AS mission_date_start,
mission.car_id AS mission_car_id,
car_1.id AS car_1_id,
car_1.car_id AS car_1_car_id,
car_1.start_date AS car_1_start_date,
car_1.end_date AS car_1_end_date,
car_1.version_start_date AS car_1_version_start_date,
car_1.version_end_date AS car_1_version_end_date,
car_gps_1.id AS car_gps_1_id,
car_gps_1.gps_id AS car_gps_1_gps_id,
car_gps_1.car_id AS car_gps_1_car_id,
car_gps_1.start_date AS car_gps_1_start_date,
car_gps_1.end_date AS car_gps_1_end_date,
gps_1.gps_code AS gps_1_gps_code
FROM mission
LEFT OUTER JOIN car AS car_1 ON car_1.car_id = mission.car_id AND
car_1.start_date <= mission.date_start
AND
car_1.end_date >= mission.date_start
AND
car_1.version_start_date <=
mission.date_start
AND
car_1.version_end_date >= mission.
date_start
LEFT OUTER JOIN car_gps AS car_gps_1 ON car_gps_1.car_id = mission.car_id
AND
car_gps_1.start_date <=
mission.date_start AND
car_gps_1.end_date >= mission.
date_start
LEFT OUTER JOIN gps as gps_1 ON gps_1.id = car_gps_1.id AND
gps_1.start_date <= mission.date_start
AND
gps_1.end_date >= mission.date_start
*So i want query, like*
query = session.query(Mission).options(
joinedload(Mission.car), joinedload(Mission.car_device)
)
results = query.all()
# and get gps code of mission car
gps_code = query.Mission.car_device.gps_code
Models code and etc.:
class GPS(Base):
'''Version of gps-device'''
__tablename__ = 'gps'
id = Column(Integer(), primary_key=True)
gps_id = Column(Integer(), primary_key=True)
start_date = Column(Date(), primary_key=True)
end_date = Column(Date(), primary_key=True)
gps_code = Column(VARCHAR(20))
class Car(Base):
'''Version of car'''
__tablename__ = 'car'
id = Column(Integer(), primary_key=True)
car_id = Column(Integer(), primary_key=True)
start_date = Column(Date(), primary_key=True)
end_date = Column(Date(), primary_key=True)
version_start_date = Column(Date(), primary_key=True)
version_end_date = Column(Date(), primary_key=True)
class CarGPS(Base):
'''Version of attachment of gps-device to car'''
__tablename__ = 'car_gps'
id = Column(Integer(), nullable=False)
gps_id = Column(Integer(), ForeignKey(GPS.gps_id), primary_key=True)
car_id = Column(Integer(), ForeignKey(Car.car_id), primary_key=True)
start_date = Column(TIMESTAMP(timezone=True), primary_key=True)
end_date = Column(TIMESTAMP(timezone=True), primary_key=True)
class Mission(Base):
'''Car mission'''
__tablename__ = 'mission'
id = Column(Integer(), primary_key=True, nullable=False)
date_start = Column(TIMESTAMP(timezone=False))
car_id = Column(Integer(), ForeignKey(Car.id))
# Version of mission car
car = relationship(Car,
primaryjoin=and_(Car.car_id == car_id,
Car.start_date <= date_start,
Car.end_date >= date_start,
Car.version_start_date <=
date_start,
Car.version_end_date >=
date_start),
foreign_keys=car_id,
uselist=False, viewonly=True)
# Version of mission car gpd-device attachment
car_gps = relationship(CarGPS,
primaryjoin=and_(CarGPS.car_id == car_id,
CarGPS.start_date <=
date_start,
CarGPS.end_date >=
date_start),
foreign_keys=CarGPS.car_id,
uselist=False, viewonly=True)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
# This query is good but without GPS device of car
query = session.query(Mission).options(
joinedload(Mission.car), joinedload(Mission.car_gps)
)
query.all()
I'am new to sqlAlchemy and can't understand how to setup such relation for
GPS device. May be it can be achieved using secondary and secondaryjoin
attributes of relationship?
Currently it can be achieved by this query:
query = query.outerjoin(CarGPS, and_(
CarGPS.car_id == Mission.car_id,
CarGPS.start_date <= Mission.date_start,
CarGPS.end_date >= Mission.date_start))
query = query.outerjoin(
GPS, and_(GPS.gps_id == CarGPS.gps_id,
GPS.start_date <= Mission.date_start,
GPS.end_date >= Mission.date_start))
But i want to do it through relations, not by join/outerjoin.
Thank you!
SQLAlchemy version: sqlalchemy==1.0.14
Python version: 3.5.1
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
