Try to move db.audio.start_time > '12:00:00' in the left join ON: left=db.audio.on((db.audio.site_id == db.sites.id) & (db.audio.start_time > '12:00:00'))
On Sunday, November 25, 2018 at 1:34:54 AM UTC+3, David Orme wrote: > > Thanks for the suggestion. I've actually tried that and get exactly the > same results - I read somewhere that using an empty db() could be > problematic, so tried both. > > It doesn't affect the result without any audio queries, but any audio > queries added into the selected set (qry in my code), cause the left join > to get reduced. I have to admit I'm not completely sure why, but the SQL > examples show it happening! > > David > > > On Sat, 24 Nov 2018, 22:18 Val K <[email protected] <javascript:> wrote: > >> 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 a topic in the >> Google Groups "web2py-users" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/web2py/RIeIVoLbGy8/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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.
