[sqlalchemy] Re: multiple M:N joins fails
Michael Bayer napsal(a): On May 1, 2007, at 4:42 AM, ml wrote: I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe- flag. ah. in that case you dont want query.join(x).join(y), you want the second join to still be relative to the original query. i think youre going to have to spell that one out explicitly for now. I don't understand what you mean by spell that one out explicitly. I need something like (recipes JOIN categories) INTERSECTION (recipes JOIN flags) or ((recipes JOIN categories) JOIN flags) generated by the ORM. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple M:N joins fails
On May 4, 2007, at 9:23 AM, ml wrote: Michael Bayer napsal(a): On May 1, 2007, at 4:42 AM, ml wrote: I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe- flag. ah. in that case you dont want query.join(x).join(y), you want the second join to still be relative to the original query. i think youre going to have to spell that one out explicitly for now. I don't understand what you mean by spell that one out explicitly. I need something like (recipes JOIN categories) INTERSECTION (recipes JOIN flags) or ((recipes JOIN categories) JOIN flags) generated by the ORM. ORM isnt going to generate your joins for you, its too complex. session.query(SomeClass).select_from(intersection(recipes.join (categories), recipies.join(flags)).select(criterion) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple M:N joins fails
I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe-flag. Michael Bayer napsal(a): On Apr 30, 2007, at 8:40 AM, ml wrote: Hi! I have 2 relations: - recipes-categories (M:N) - recipes-flags (M:N) I'd like to get something like: SELECT recipes.title FROM recipes JOIN _recipes_ctgs_recipes ON _recipes_ctgs_recipes.id_recipe = recipes.id JOIN recipes_ctgs ON _recipes_ctgs_recipes.id_recipes_ctg=recipes_ctgs.id JOIN _recipes_flgs_recipes ON _recipes_flgs_recipes.id_recipe = recipes.id JOIN recipes_flgs ON _recipes_flgs_recipes.id_recipes_flg=recipes_flgs.id WHERE recipes_ctgs.title='cat1' AND recipes_flgs.title='flag1' when I run sess.query(Recipe).join(ctgs).join(flgs).select(...) it fails with sqlalchemy.exceptions.SQLError: (ProgrammingError) table name _recipes_ctgs_recipes specified more than once where _recipes_ctgs_recipes is a secondary table. Full example attached. well, yeah, youre joining against the same relationship twice. going from ctgs to flgs makes it essentially a self referential join on recipes. i dont understand what youre trying to query for there but my intuition tells me theres probably some better way to lay out that query without 5 joins in between. if not, youll have to lay out the self referential part manually using table aliases. (note to SA old schoolers - see why i hesitated so much to add auto- joins across relationships ? every new feature spawns a whole new class of user issues) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple M:N joins fails
On May 1, 2007, at 4:42 AM, ml wrote: I want to get recipes which belongs to a particular category and having a particular flag. So I need both joins recipe-category and recipe- flag. ah. in that case you dont want query.join(x).join(y), you want the second join to still be relative to the original query. i think youre going to have to spell that one out explicitly for now. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple M:N joins fails
On Apr 30, 2007, at 8:40 AM, ml wrote: Hi! I have 2 relations: - recipes-categories (M:N) - recipes-flags (M:N) I'd like to get something like: SELECT recipes.title FROM recipes JOIN _recipes_ctgs_recipes ON _recipes_ctgs_recipes.id_recipe = recipes.id JOIN recipes_ctgs ON _recipes_ctgs_recipes.id_recipes_ctg=recipes_ctgs.id JOIN _recipes_flgs_recipes ON _recipes_flgs_recipes.id_recipe = recipes.id JOIN recipes_flgs ON _recipes_flgs_recipes.id_recipes_flg=recipes_flgs.id WHERE recipes_ctgs.title='cat1' AND recipes_flgs.title='flag1' when I run sess.query(Recipe).join(ctgs).join(flgs).select(...) it fails with sqlalchemy.exceptions.SQLError: (ProgrammingError) table name _recipes_ctgs_recipes specified more than once where _recipes_ctgs_recipes is a secondary table. Full example attached. well, yeah, youre joining against the same relationship twice. going from ctgs to flgs makes it essentially a self referential join on recipes. i dont understand what youre trying to query for there but my intuition tells me theres probably some better way to lay out that query without 5 joins in between. if not, youll have to lay out the self referential part manually using table aliases. (note to SA old schoolers - see why i hesitated so much to add auto- joins across relationships ? every new feature spawns a whole new class of user issues) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
