SQLFORM.grid multiple left joins and where clause

[A36_Marty]

A few novice questions while learning the SQLFORM.grid on a throw-away app.   My apologies for the basic questions

1) How to show all users (auth_user) and related group roles (auth_group.role) only for certain auth_groups?

I can display *all* groups with the following code, but can't seem to figure out how to get the WHERE clause to SQLFORM.grid.

    query = db.auth_user

    

    fields = db.auth_user.first_name, db.auth_group.role

    

    left = db.auth_user.on(

        db.auth_user.id == db.auth_membership.user_id).db.auth_membership.on(

            db.auth_membership.group_id == db.auth_group.id)

    grid = SQLFORM.grid(query = query, left=left, fields=fields)

My failed attempts to get the WHERE clause inserted:

query = (db.auth_group.id == PARENT_GROUP_ID)  #No auth_user fields shown

query = (db.auth_user)&(db.auth_group.id == PARENT_GROUP_ID)  #get query not supported error by the grid

2) How to custom-format a column based on field values?

In the above grid example, if an auth_group.role of "Parent" is returned, how can show something like a check mark (or any like boolean yes/no flag) and nothing for anything other auth_group.role values?

Thanks in advance.   A little nudge getting me through some the syntax learning curve is much appreciated.

[Johann Spies]

On 10 May 2015 at 04:26, A36_Marty <ma...@mccaslandcorp.com> wrote:

    fields = db.auth_user.first_name, db.auth_group.role

Make that

fields = [db.auth_user.first_name, db.auth_group.role]

fields should be a list.

query = (db.auth_user)&(db.auth_group.id == PARENT_GROUP_ID)  #get query not supported error by the grid

You did have the variable 'query' defined two times. Is the second one supposed to replace the first one.

And you did not show where you get PARENT_GROUP_ID from.

2) How to custom-format a column based on field values?

In the above grid example, if an auth_group.role of "Parent" is returned, how can show something like a check mark (or any like boolean yes/no flag) and nothing for anything other auth_group.role values?

You can write a function to use with field.represent and use that.

Here is an example of what I have used in the past.

def get_url(url, record):
    if url:
        address = A('click here to see pdf', _target = "_blank",
                    _href = "some_prefix'%s'" % url)
    else:
        address = ''
    return address

And in the model:

                Field("url", represent = lambda x, record: get_url(x,record)),

Regards

Johann

--

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

[A36_Marty]

Thanks for your response.

I changed the fields to a list and that works fine.  Ditto for the field.represent feature.  Thanks!

I am still having problems getting the WHERE clause correctly inserted in the query.   (The 2-3 different query assignments I originally posted were variations I had tried with no luck.)

I would like the query string for a SQLGrid that accomplishes the same as the following DAL:

    query = db(db.auth_group.id == PARENT_GROUP_ID).select(

        db.auth_user.last_name, db.auth_user.first_name, db.auth_group.role,

        left = db.auth_user.on(db.auth_user.id==db.auth_membership.user_id

                               ).db.auth_membership.on(db.auth_membership.group_id == db.auth_group.id))

The above DAL works fine if I am simply outputting it as HTML.  However, if I supply it as a the query parameter to SQLFORM.grid, I get an error from the grid.

Ideally I would like to create a SQLFORM.grid that matches the following SQL:

Select auth_user.first_name, auth_user.last_name, auth_group.role FROM auth_user

LEFT OUTER JOIN auth_membership ON auth_user.id = auth_membership.user_id

LEFT OUTER JOIN auth_group ON auth_membership.group_id = auth_group.id

WHERE auth_group.id = 64; 

 

Any help in pointing out how to get that WHERE clause correctly supplied to the SQLFORM.grid is appreciated.  

[A36_Marty]

I figured out my problem.

By changing the query to:

query = db.auth_user.id>0
query = query&db.auth_group.id==SOME_VALUE

I was able to get the WHERE clause into the grid AND still be able to access fields in both tables.    Previously I was only specifying query=db.auth_group.id == VALUE, which worked, but I could only show fields from the auth_group table in the grid.

Elementary to most on this group, I'm sure, but I am posting back just in case another novice like me has the same problem.