Left join with filters on joined table

[David Orme]

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

[Val K]

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) 

[David Orme]

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

--
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.

[Val K]

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'))

[David Orme]

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