I don't have the raw SQL that performs this query so I'll take your advice on posting to SO to see what is needed.
Thanks, -Jim On Monday, October 23, 2017 at 10:07:10 AM UTC-4, Mike Bayer wrote: > > On Sat, Oct 21, 2017 at 11:21 AM, Jim Shepherd <[email protected] > <javascript:>> 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
