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 <valq7...@gmail.com 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
> web2py+unsubscr...@googlegroups.com.
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
