On Sat, Oct 21, 2017 at 11:21 AM, Jim Shepherd <[email protected]> wrote: > 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.
are you able to create a raw SQL statement that does the thing you need? I can help you translate any SQL into a SQLAlchemy-style query. Working out the actual SQL here is a lot more work, however you can likely get help with that part from a place like stackoverflow if nobody here is able to assist, just phrase your question in terms of CREATE TABLE and SELECT statements and tag it as "SQL", so the generic audience of SQL developers can chip in. > > 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. -- 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.
