I think you want
father=db.dog.with_alias("father")
mother=db.dog.with_alias("mother")
rows = db().select(db.dog.name, father.name, mother.name,
left=(father.on(father.id==db.dog.father_id),
mother.on(mother.id==db.dog.mother_id)))
On Oct 11, 3:01 pm, Maciek Sykulski <[email protected]> wrote:
> Hi Everyone,
>
> I'm using web2py Version 1.67.2 (2009-09-28 16:29:33) with MySQL
> and when I execute a following example I get an error.
> in db.py:
> db.define_table("dog",
> SQLField("name"),
> SQLField("father_id", "reference dog"),
> SQLField("mother_id", "reference dog"))
> in index():
> father=db.dog.with_alias("father")
> mother=db.dog.with_alias("mother")
> rows = db().select(db.dog.name, db.father.name, db.mother.name,
> left=(db.father.on
> (db.father.id==db.dog.father_id),
> db.mother.on(db.mother.id==db.dog.mother_id)))
> I get an error:
> ProgrammingError: (1064, "You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server version for the right
> syntax to use near 'LEFT JOIN dog ON dog.id=dog.father_id LEFT JOIN
> dog ON dog.id=dog.mother_id' at line 1")
>
> In fact the _select(...) on the query returns the following sql:
> 'SELECT dog.name, dog.name, dog.name FROM LEFT JOIN dog ON
> dog.id=dog.father_id LEFT JOIN dog ON dog.id=dog.mother_id;'
> which does not have aliases inside?!?
>
> I found this problem in my installation after trying to write my own
> query using with_alias, and failed (I was getting queries without
> aliases, that is: all aliases were the same original table in SQL)
>
> Does anybody have a clue what might be going on here?
> Is it only me, or the latest version of web2py? (I had the same with
> 1.66, upgraded and still same problem)
>
> Thanks,
> m
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
