Although the web2py documentation (page 165) appears to imply that the
argument distinct can only be set to True or False, the gluon/sql.py
file reveals otherwise. From line 2871 of sql.py:
distinct = attributes.get('distinct', False)
if distinct == True:
sql_s += ' DISTINCT'
elif distinct:
sql_s += ' DISTINCT ON %s' % distinct
So, it appears as though the code is trying to support providing
specific field names with which to execute the DISTINCT ON expression
upon (the "elif distinct" part). I found a post about this from June
when it looks like the above code was first proposed:
http://groups.google.com/group/web2py/browse_thread/thread/cc6180d335b14d62/4f61ffa4ee14c1d5?lnk=gst&q=DISTINCT#4f61ffa4ee14c1d5.
The code, however, does not appear to be executing properly. Whether
distinct is set to True or, more importantly, whether it is set to a
field name, the "distinct == True" branch is still taken and the "elif
distinct" branch is never executed (unfortunately in my case). I could
be doing something wrong, so let me try to include the relevant
information.
I have the following table:
db.define_table('sensor_health',
Field('deployment_site', 'string'),
Field('sensor_name', 'string'),
Field('sensor_started', 'datetime'),
Field('last_heard_from', 'datetime'),
Field('sampling_rate', 'double'),
Field('sensor_event_count', 'integer')
)
When executing this query:
sensorHealthRows= db(db.sensor_health.deployment_site ==
deploymentSite).select(
db.sensor_health.sensor_name, db.sensor_health.sensor_started,
db.sensor_health.last_heard_from,
db.sensor_health.sampling_rate,
db.sensor_health.sensor_event_count,
orderby=(db.sensor_health.sensor_name|
~db.sensor_health.sensor_started),
distinct=db.sensor_health.sensor_name)
Which should be equivalent to this in PostgresSQL:
SELECT DISTINCT ON (sensor_health.sensor_name)
sensor_health.sensor_name, sensor_health.sensor_started,
sensor_health.last_heard_from, sensor_health.sampling_rate,
sensor_health.sensor_event_count
FROM sensor_health
ORDER BY sensor_health.sensor_name, sensor_health.sensor_started
DESC;
I set a breakpoint in sql.py at line 2872 in Wing IDE and the code
does indeed follow the "distinct == True" branch even though I clearly
set "distinct = db.sensor_health.sensor_name" in my query. Here's the
even stranger part: using Wing IDE's Debug Probe, I could actually
play around with this a bit (a direct copy/paste from the output is
below). Notice that the type(distinct) is class 'gluon.sql.Field' and,
perhaps even more interesting, it appears as though distinct evaluates
to both True and False. What's going on here?
>>> print distinct
sensor_health.sensor_name
>>> distinct == True
<gluon.sql.Query object at 0x0393E510>
>>> if distinct == True: print "distinct is True"
...
distinct is True
>>> if distinct == False: print "distinct is False"
...
distinct is False
>>> type(distinct)
<class 'gluon.sql.Field'>
Thanks for your help!
j
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
