I do not not know but in the DAL query I see:
db.tbl_objects.rlord
In the original query I see tbl_objects.horid and tbl_alt_objects.rlord but
not tbl_objects.rlord.
I do not know the model but I suspect you simply have some typos.
On Thursday, 20 September 2012 06:35:31 UTC-5, Pradeeshnarayan wrote:
>
> I am trying to convert the below sql query to DAL query.
>
> SQL query:
> SELECT L1_alt_obj.objid,L1_obj.objid
> FROM tbl_alt_objects
> JOIN tbl_objects on tbl_objects.horid = tbl_alt_objects.horid
> AND tbl_objects.objid = tbl_alt_objects.objid
> LEFT JOIN tbl_objects L1_obj ON L1_obj.horid = tbl_objects.horid
> AND L1_obj.objid = tbl_objects.rlord
> LEFT JOIN tbl_alt_objects L1_alt_obj ON L1_alt_obj.horid =
> tbl_objects.horid
> AND L1_alt_obj.objid = tbl_alt_objects.rlord
> WHERE tbl_alt_objects.horid=1076
> AND tbl_alt_objects.objid=1
> SQL is working fine and getting result
> +-------+-------+
> | objid | objid |
> +-------+-------+
> | 4 | 5 |
> +-------+-------+
>
>
> DAL query:
>
> L1_obj = db.tbl_objects.with_alias('l1_obj')
> L1_alt_obj = db.tbl_alt_objects.with_alias('l1_alt_obj')
> query = (db.tbl_alt_objects.horid==1076) & \
> (db.tbl_alt_objects.horid==db.tbl_objects.horid) & \
> (db.tbl_objects.objid==1) &
> (db.tbl_alt_objects.objid==db.tbl_objects.objid)
> result = db(query).select(L1_obj.objid,L1_alt_obj.objid,
> left=[
> L1_obj.on((L1_obj.horid==horid) &
> (db.tbl_objects.rlord == L1_obj.objid)),
> L1_alt_obj.on((L1_alt_obj.horid==horid) &
> (L1_alt_obj.objid==db.tbl_alt_objects.rlord))
> ]
> ).first()
>
> But I am getting an error as
>
> File
> "/var/www/vhosts/astrozon/web2py/applications/astrozon/controllers/horoscope.py",
>
> line 104, in testing
> L1_alt_obj.on((L1_alt_obj.horid==horid) &
> (L1_alt_obj.objid==db.tbl_alt_objects.rlord))
> File "/var/www/vhosts/astrozon/web2py/gluon/dal.py", line 7578, in select
> return adapter.select(self.query,fields,attributes)
> File "/var/www/vhosts/astrozon/web2py/gluon/dal.py", line 1315, in select
> rows = response(sql)
> File "/var/www/vhosts/astrozon/web2py/gluon/dal.py", line 1305, in
> response
> self.execute(sql)
> File "/var/www/vhosts/astrozon/web2py/gluon/dal.py", line 1392, in
> execute
> return self.log_execute(*a, **b)
> File "/var/www/vhosts/astrozon/web2py/gluon/dal.py", line 1386, in
> log_execute
> ret = self.cursor.execute(*a, **b)
> File "/var/www/vhosts/astrozon/web2py/gluon/contrib/pymysql/cursors.py",
> line 108, in execute
> self.errorhandler(self, exc, value)
> File
> "/var/www/vhosts/astrozon/web2py/gluon/contrib/pymysql/connections.py",
> line 184, in defaulterrorhandler
> raise errorclass, errorvalue
> InternalError: (1054, u"Unknown column 'tbl_objects.rlord' in 'on clause'")
>
--
