Thanks, I hadn't realised that db.table.on() could be passed more complex
queries. I'd tried feeding a list of queries - the online book mentions
that left= can take an iterable - but each list item creates a separate
left join statement. Being able to pass a single query including qualifiers
is exactly what I needed.
I particularly wanted to be able to create the db.table.on() dynamically,
to add a set of filters if requested and that turns out to be easy -
create the main left join as a query object and then &= on filter if there
are any, before passing that to db.table.on()
filters = (db.audio.start_time > '12:00:00')
qry = (db.audio.site_id == db.sites.id)
if filters is not None:
qry &= filters
data = db().select(db.sites.ALL,
db.audio.id.count().with_alias('n_audio'),
left=db.audio.on(qry),
groupby=db.sites.id)
Thanks again,
David
On Saturday, 24 November 2018 23:20:31 UTC, Val K wrote:
>
> 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 <valq...@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+un...@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.
