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'")
--
