Hi,
I've got a table 'sites' and a table 'audio', where sites.id =
audio.site_id. I'm trying to create a service that returns a list of sites
with the number of audio recordings at each site, including zero counts.
That's easily achieved using:
sitedata = db(db.sites).select(db.sites.ALL,
db.audio.id.count().with_alias('n_audio'),
left=db.audio.on(db.audio.site_id == db.sites
.id),
groupby=db.sites.id)
The problem arises when I want to count only a subset of the audio table,
controlled by variables passed in with the call. At the moment, I'm trying
to implement that by chaining together queries to add in filters. So for
example:
qry = db(db.sites)
# code modifies qry, such as:
qry = qry(db.audio.start_time > '12:00:00')
sitedata = qry.select(db.sites.ALL,
db.audio.id.count().with_alias('n_audio'),
left=db.audio.on(db.audio.site_id == db.sites.id),
groupby=db.sites.id)
Now that runs, but it loses the entry for each site, retaining only those
where some data is present. The filtered example above produces:
SELECT sites.id, ... , COUNT(audio.id) AS n_audio
FROM sites LEFT JOIN audio ON (audio.site_id = sites.id)
WHERE ((sites.id IS NOT NULL)
AND (audio.start_time > '12:00:00'))
GROUP BY sites.id;"
As I understand it, that where clause outside of the Left Join filters the
result of the join, dropping rows, and what I need to achieve is to move
the filter clauses inside the left join. The filters are going to need to
include > < as shown but also an option using .belongs() to test for
particular values.
SELECT sites.id, ..., COUNT(audio.id) AS n_audio
FROM sites LEFT JOIN audio
ON (audio.site_id = sites.id)
AND ((sites.id IS NOT NULL)
AND (audio.start_time > '12:00:00'))
GROUP BY sites.id;
That last query generates the output I need but I can't figure out how to
pass them into the DAL. Any suggestions? I'm keen on the concept of passing
request.vars to a handler that can be shared by different calls and returns
a suitably tweaked subset of audio to be searched, but it seems like I need
to pass the filters in somewhere else.
Thanks,
David
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
