Help - Grid with left joins and sending arguments/variables to other functions

[Matthew J Watts]

Hi all

I'm having some problems  trying to achieve the following  

I have a grid with left joins which selects a subset of entries in my database -(query between 3 tables) 

 i then want to be able to set up links within the grid that sends the id from each row (MAIN table) to a seperate function (another query between many tables). 

 

I then want to trigger a download 'csv' file. 

So far, I have managed to get this working if I use one table in the grid  (no left joins), however when i set up left joins within the grid,i get an 'AttributeError' message. Somehow, by setting up the left joins, i loose the main id that i  want to send to the seperate function - although i have set up  'field_id=db.MAIN.id' in the grid

Here's the code:

Controller

def vec_dyn_query():

    links = [lambda ids: A('Download data set',_href=URL("default","download_dataset.csv",args=[ids.id]))]

    grid = SQLFORM.grid((db.MAIN), 

                        field_id=db.MAIN.id,

                        left = [db.StudyLocation.on(db.MAIN.LocationID == db.StudyLocation.id), db.TAXON.on(db.MAIN.TaxonID == db.TAXON.id)],

                        links=links,

                        deletable=False, editable=False, details=False, selectable=False, create=False, csv=False)

    return locals()

def download_dataset():

    main_id = request.args(0,cast=int)

    dataset = db(db.MAIN.id == request.args[0]).select()

    return dict(dataset=dataset)

Views

{{

import cStringIO

stream = cStringIO.StringIO()

dataset.export_to_csv_file(stream)

response.headers['Content-Type'] = 'text/csv'

response.write(stream.getvalue(), escape=False)

}}

Note - if i take away the 'left = [db.StudyLocation.on(db.MAIN.LocationID == db.StudyLocation.id), db.TAXON.on(db.MAIN.TaxonID == db.TAXON.id)],' Everything works fine

Thanks for your help in advance

Matt

[Jim S]

I'd start first with changing this:

links = [lambda ids: A('Download data set',_href=URL("default","download_dataset.csv",args=[ids.id]))]

to this:

links = [lambda ids: A('Download data set',_href=URL("default","download_dataset.csv",args=[ids.MAIN.id]))]

When you add the LEFT join you then need to specify which table to get the field from.

-Jim

[Matthew J Watts]

Great thanks Jim, that did it for me - it's hard being a beginner!!

--
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/pH_iiWr43UI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jim S]

We were all there once.  Glad I was able to help.  Part of the beauty of web2py is the community and the friendly help you can get on this group.

-Jim

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Matthew J Watts]

Hi, do you know if this is the same when i use primary keys of legacy tables - doesn't seem to be working when i change my tables to primary key. thanks

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Jim Steil]

I'm assuming you're talking about a primary key made up of multiple fields.  Is that correct.

I've never done that, but I don't see how it would change anything other than the 'left' setup.

-Jim

[Matthew J Watts]

Ok thanks for your help Jim, i was getting confused - i introudced a bug into my app but all working fine now i moved all the code to a fresh app

[Jim Steil]

I've certainly done that in the past as well.

Have a great weekend!

-Jim