try remove db.sites from query (just db().select(...))
db(db.sites) is shortcut for db(db.sites.id>0) which turns into WHERE ((
sites.id IS NOT NULL)
On Sunday, November 25, 2018 at 1:00:56 AM UTC+3, David Orme wrote:
>
> 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.
