Hi,
I've got table Ad which has many to many relation with table Equipment
through table ad_equipment. I want to select all ads which have all
equipments in the list.
Corresponding SQL query will look like this:
select * from ad where ad.id in (
select id from ad
join ad_equipment eq1 on ad.id = eq1.ad
join equipment e1 on eq1.equipment = e1.id
join ad_equipment eq2 on ad.id = eq2.ad
join equipment e2 on eq2.equipment = e2.id
where e1.name = 'name1' and e2.name = 'name2'
)
I've tried this the following:
eq1 = ad_equipment_table.alias()
eq2 = ad_equipment_table.alias()
e1 = equipment_table.alias()
e2 = equipment_table.alias()
ad_table.join(eq1).join(e1).join(eq2).join(e2)
but this corresponds to:
select * from ad
JOIN ad_equipment AS ad_equipment_1 ON ad.id = ad_equipment_1.ad
JOIN equipment AS equipment_1 ON equipment_1.id = ad_equipment_1.equipment
JOIN ad_equipment AS ad_equipment_2 ON equipment_1.id =
ad_equipment_2.equipment AND ad.id = ad_equipment_2.ad
JOIN equipment AS equipment_2 ON equipment_2.id =
ad_equipment_1.equipment AND equipment_2.id = ad_equipment_2.equipment
which is not what I want as it joins e2 to be the same as e1...
Is it possible to write that SQL select in SA expression language?
thanks,
tomas
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
