smartgrid query on 2 or more tables

[Roderick]

Hi web2py users!

1. Can anyone tell me if the query input field on SQLform smartgrid
forms can do a join on 2 or more tables?
2. If so, how do I construct such a query? My attempts my failed and
can't find docs on this either...

Note - I would like users to be able to construct and type in their
own queries directly in the smartgrid query field. As these queries
will be dynamic, doing this programmatically will be difficult - if
the query can handle joins or fields from multiple tables then this
should be "pie" :)

Thanks!

[Johann Spies]

On 29 February 2012 13:35, Roderick <roderi...@gmail.com> wrote:

Hi web2py users!

1. Can anyone tell me if the query input field on SQLform smartgrid
forms can do a join on 2 or more tables?

If you use SQLFORM.grid the answer is 'Yes'
 

2. If so, how do I construct such a query? My attempts my failed and
can't find docs on this either...

Here is a small example:

 query = ((db.akb_doccenter.location == db.akb_doccenter_location.uuid)&
             (db.akb_doccenter.category == db.akb_doccenter_category.uuid))

  Fields = [db.akb_doccenter.title, db.akb_doccenter.author, db.akb_doccenter.publication_date,
               db.akb_doccenter.doc_nr, db.akb_doccenter_location.location, db.akb_doccenter_category.category]
    fields = columns
    opskrif = H2(T('Document Center Contents'))

    links = [lambda row: A('Edit' , _href = URL('doccenter', 'akb_docedit',
                                                        args = [row.id]))]
    if auth.is_logged_in() and auth.has_membership('doccenter', auth.user.id):
        data = SQLFORM.grid(query, fields = fields,
               orderby = db.akb_doccenter.title | db.akb_doccenter.publication_date,
               #links = links, editable = False,
                        maxtextlength = 60)
    else:

       data = SQLFORM.grid(query, fields = fields, orderby = db.akb_doccenter.title | db.akb_doccenter.publication_date,
                       deletable = False, editable = False, maxtextlength = 60)

Note - I would like users to be able to construct and type in their
own queries directly in the smartgrid query field. As these queries
will be dynamic, doing this programmatically will be difficult - if
the query can handle joins or fields from multiple tables then this
should be "pie" :)

Thanks!

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Johann Spies]

Apololgies, Gmail's web interface and I am not always friends.  The above message was sent before I have finished it.

Users can also build dynamic query (as long as all the tables are presented in the original query) using the search function (query-button) that comes with the grid.

My experience is that users do not find that function very easy to use, so you might have to provide them with some guidelines and/or examples.

Regards
Johann

[Roderick]

Thanks Johann!

1. Can this be done with smartgrid?
2. Can you give me an example of a dynamic query (e.g. what users
would type into the search field)? (for grid or smartgrid).

Thanks!

[Johann Spies]

On 29 February 2012 16:44, Roderick <roderi...@gmail.com> wrote:

Thanks Johann!

1. Can this be done with smartgrid?

Not in a similar way.  You can read more about that in http://www.web2py.com/books/default/chapter/29/7?search=smartgrid#SQLFORM.grid-and-SQLFORM.smartgrid-%28experimental%29
 

2. Can you give me an example of a dynamic query (e.g. what users
would type into the search field)? (for grid or smartgrid).

Here is one built with the query button on one of my grids:

akb_articles.title contains "water" and akb_articles.pubyear > "1990" and akb_articles.pubyear <= "2010"

Experiment for yourself and explore the options.


There is also a slice with examples on using SQLFORM.grid at http://www.web2pyslices.com/slices/take_slice/148

 Regards
Johann

[Roderick]

Hi Johann

I'm really struggling to figure out the DAL query syntax (the
documentation I could find has minimalistic examples)...

So, can you please help me - how do I do this:

"as long as all the tables are presented in the original query"

query = ?
form = SQLFROM.grid(query,...)

I.e. I need a grid that displays all data from all 10 of my tables so
my users can run a complex query on any of them including joins?
(Note 1: I would prefer the original grid to be blank - having all
rows from all tables would be meaningless and crowded)
(Note 2: I have achieved some degree of success trying to do this from
"appadmin" but that doesn't use grids :( and I'd prefer to give my
users a cleaner interface... )

Thanks!

Roderick

On Mar 1, 8:51 am, Johann Spies <johann.sp...@gmail.com> wrote:

> On 29 February 2012 16:44, Roderick <roderick.m...@gmail.com> wrote:
>
> > Thanks Johann!
>
> > 1. Can this be done with smartgrid?
>

> Not in a similar way.  You can read more about that inhttp://www.web2py.com/books/default/chapter/29/7?search=smartgrid#SQL...

>
> > 2. Can you give me an example of a dynamic query (e.g. what users
> > would type into the search field)? (for grid or smartgrid).
>
> Here is one built with the query button on one of my grids:
>
> akb_articles.title contains "water" and akb_articles.pubyear > "1990" and
> akb_articles.pubyear <= "2010"
>
> Experiment for yourself and explore the options.
>

> There is also a slice with examples on using SQLFORM.grid athttp://www.web2pyslices.com/slices/take_slice/148

[Johann Spies]

On 5 March 2012 15:21, Roderick <roderi...@gmail.com> wrote:

So, can you please help me - how do I do this:

"as long as all the tables are presented in the original query"

query = ?
form = SQLFROM.grid(query,...)

I.e. I need a grid that displays all data from all 10 of my tables so
my users can run a complex query on any of them including joins?

Give me an example of a raw sql query that will do what you want.

Appadmin uses SQLTABLE which is easy to use with complex queries. 

There are at least two plugins which can be used with complex queries:

Powertable http://powertable.blouweb.com/
and
Solidtable http://dev.s-cubism.com/plugin_solidtable

Regards
Johann