I have rechecked again. There is a valid field rlord in tbl_objects.
You can see the field 'tbl_objects.rlord' in the 6th line of SQL query. SQL
is working fine.
In the error description I have noticed a difference.
"
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))
"
here it is showing the 104 line has problem.
but the in last line "InternalError: (1054, u"Unknown column
'tbl_objects.rlord' in 'on clause'")". it is telling the error is in
field 'tbl_objects.rlord' which is in the previos line(103).
I think it is a small syntax error. But I am unable to find out that.
On Thursday, 20 September 2012 15:34:30 UTC+2, Massimo Di Pierro wrote:
>
> 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'")
>>
>
--
