I am trying to create a recursive that follows two branches during each
recursion. The data model captures operations that can be performed on
series of data or collectively on sets of series of data. Each operation
wither performs on the data set or data series level. These operations can
be stacked creating chains, i.e., (data sets or data
series)->operations->data set. I am not able to create a recursive CTE that
can following either the data set->operation->data set or data set->data
series->operation->data set paths during each recursion.
Here are the primary components of the model:
class DataSeries(Base):
__tablename__ = 'DataSeries'
key = Column('DataSeriesId', Integer,
Sequence('DataSeries_SEQ'),
primary_key=True)
dataSetId = Column('DataSetId', Integer,
ForeignKey('DataSet.DataSetId',
name='DataSeries_DataSet_FK'))
class DataSet(Base):
__tablename__ = 'DataSet'
key = Column('DataSetId', Integer,
Sequence('DataSet_SEQ'),
primary_key=True)
class DataOperation(Base):
__tablename__ = 'DataOperation'
key = Column('DataOperationId', Integer,
Sequence('DataOperation_SEQ'),
primary_key=True)
processedDataSetId = Column('ProcessedDataSetId', Integer,
ForeignKey('DataSet.DataSetId',
name='DataOperation_DataSet_FK'))
class DataOperationDataSeries(Base):
__tablename__ = 'DataOperationDataSeries'
operationId = Column('DataOperationId', Integer,
ForeignKey('DataOperation.DataOperationId',
name='DataOpDataSeries_DataOp_FK'),
primary_key=True, nullable=False)
dataSeriesId = Column('DataSeriesId', Integer,
ForeignKey('DataSeries.DataSeriesId',
name='DataOpDataSeries_DataSeries_FK'),
primary_key=True, nullable=False)
class DataOperationDataSet(Base):
__tablename__ = 'DataOperationDataSet'
operationId = Column('DataOperationId', Integer,
ForeignKey('DataOperation.DataOperationId',
name='DataOpDataSet_DataOperation_FK'),
primary_key=True, nullable=False)
dataSetId = Column('DataSetId', Integer,
ForeignKey('DataSet.DataSetId',
name='DataOpDataSet_DataSet_FK'),
primary_key=True, nullable=False)
The following CTE does not seem to follow either branch. Removing the union
line will successfully follow of the Data Set->Operation->Data Set path as
expected.
dsIncluded = session.query(DataSet.key) \
.filter(DataSet.key == 1)
.cte(name='dsIncluded', recursive=True)
previousDS = aliased(dsIncluded)
currentDS = aliased(DataSet)
dataSetAlias = aliased(DataSet)
dsIncluded = dsIncluded.union_all(
session.query(currentDS)
.join(DataOperationDataSet),
.union(DataSeries, DataOperationDataSeries)
.join(DataOperation)
.join(dataSetAlias)
.filter(dataSetAlias.key == previousDS.c.DataSetId)
)
I have tried many other options with different joins and unions but with my
lack of experience in SQL I am not able to figure out how to get his to
work. Any help is appreciated. Let me know if more information would be
helpful.
Thanks,
Jim
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
