Hi,
I have trouble on making a query between two tables:
ProcessedDataset
- ID
ProcDSParent
- ThisDataset
- ItsParent
To find the `Parent` and `Child` of a `Dataset`, I need explicit
specify the join sequence between:
`ProcessedDataset` <-- `ProcDSParent` --> `ProcessedDataset`
After make different alias for them, I try different join approach,
but still not able to get it.
I will demonstrate with find `Parent` of a `Dataset`:
---------------------------------------------------------------------
dataset = DB.db_tables[db_alias]['ProcessedDataset']
dsparent = DB.db_tables[db_alias]['ProcDSParent']
parent = dataset.alias('ProcessedDataset_ItsParent')
child_parent = dsparent.alias('ThisDataset_ItsParent')
keylist = [parent.c.Name]
whereclause = dataset.c.Name.op('=')('Test')
---------------------------------------------------------------------
--- Approach <1>:
* FromClause.join + expression.select
---------------------------------------------------------------------
r_join = dataset
r_join.join(child_parent, \
dataset.c.ID == child_parent.c.ThisDataset)
r_join.join(dsparent, \
child_parent.c.ItsParent == parent.c.ID)
query = select(keylist, from_obj=r_join, whereclause=whereclause)
---------------------------------------------------------------------
* result <1>:
===============================================
SELECT `ProcessedDataset_ItsParent`.`Name`
FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`,
`ProcessedDataset`
WHERE `ProcessedDataset`.`Name` = %s
===============================================
--- Approach <2>:
* orm.join + expression.select
---------------------------------------------------------------------
join2 = join(dataset, child_parent, \
dataset.c.ID == child_parent.c.ThisDataset)
join2.join(dsparent, \
child_parent.c.ItsParent == parent.c.ID)
---------------------------------------------------------------------
* result <2>:
===============================================
SELECT `ProcessedDataset_ItsParent`.`Name`
FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`,
`ProcessedDataset` INNER JOIN `ProcDSParent`
AS `ThisDataset_ItsParent` ON
`ProcessedDataset`.`ID` = `ThisDataset_ItsParent`.`ThisDataset`
WHERE `ProcessedDataset`.`Name` = %s
===============================================
Appreciate for any help, stuck here for couple of days already!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
