web2py dynamic queries

[Cristina Sig]

Hello,

I'm a newbie on python language so I'm struggling to do some queries.

I have a table Student which I use to fill a table on view, above that table, I have a search box, where I would like to provide the option to search in multiple columns (phone, last name, first name, or nationality).

I don't know how exactly do a dynamic query in this case because I have some fields which are references from other tables.

This is my Db

db.define_table('Nationality',
                Field('descripcion', 'string'),
               )

db.define_table('Grade',
                Field('level', 'string'),
               )

db.define_table('Student',
                Field('lastname', 'string'),
                Field('firstname', 'string'),
                Field('nationality','reference Nationality'),
                Field('phone', 'integer'),
                Field('email', 'string'),
                Field('gradelevel','reference Grade'),
               )

and this is my try so far

queries = [(db.Student.id > 0)]
    if search_value and search_value != '' and search_value != 0:
        queries.append(db.Student(search_value)) 
        query = reduce(lambda a,b:(a&b),queries)

        query.select()

I found a generic way to do it but still don't know how to deal with references.
Any suggestion/idea would be very appreciated :)

queries=[]
if arg1 == "xyz": queries.append(db.abc.id > 0)
if arg2 == "xyz": queries.append(db.def.id > 0)
query = reduce(lambda a,b:(a&b),queries)
query.select()

[John Bannister]

Hi Cristina,

 

Can you show us your view code as well?

 

Assuming you are trying to use datatables instead of web2py Grid or Smartgrid (which gives you ability to build dynamic queries), you will probably need to loop through all the fields in the table (server side) and build your query based on the value entered in the serach box.

 

Referenced fields will generally be displayed as the id of the referencing field depending on how you pass the information back to your view (i.e rendered or not) so you will only be able to search on the value in the view.

 

Alternatively you can look at the individual field.type (which will tell you the type of field you are dealing with) and treat each field type accordingly.

 

Hope this helps

John

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/54dc8ca8-bebe-4604-8e4e-a89c254ed944%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jim S]

Cristina

I'd look at adding a 'left' argument on my query.select().

if search_value and search_value != '' and search_value != 0:

    queries.append((db.Student.firstname.contains(search_value)) |
                   (db.Student.lastname.contains(search_value)) |
                   (db.Student.phone.contains(search_value)) |
                   (db.Nationality.descripcion.contains(search_value))

query = reduce(lambda a, b: (a & b), queries)
query.select(left=db.Nationality.on(db.Student.nationality == db.Nationality.id))

The above should give you the rows where the search text matches firstname or lastname or phone or nationality description.  You may want to change that to 'and' instead of 'or' depending on your requirements.

Make sense?

-Jim

[Cristina Sig]

Hello Jim,

I tried the code and it shows me an error

DataTables warning: table id=tableStudent - Ajax error. For more information about this error, please see http://datatables.net/tn/7

I think it is not recognising the numeric field or the referenced ones

[Jim S]

I'm guessing it's the phone number field.  You can't use 'contains' on a numeric field.  So, try this:

if search_value and search_value != '' and search_value != 0:

    try:
        int_search_value = int(search_value)
        queries.append((db.Student.firstname.contains(search_value)) |
                       (db.Student.lastname.contains(search_value)) |
                       (db.Student.phone == int_search_value) |
                       (db.Nationality.descripcion.contains(search_value))

    except:
        queries.append((db.Student.firstname.contains(search_value)) |
                       (db.Student.lastname.contains(search_value)) |

                       (db.Nationality.descripcion.contains(search_value))
       
query = reduce(lambda a, b: (a & b), queries)
query.select(left=db.Nationality.on(db.Student.nationality == db.Nationality.id))

Basically, if you can convert the search_value to an int, you want to check the phone number against the int as well.  If you can't convert to an int, then don't include phone number in the search.

The datatables error is not very helpful.  What you want to look at is the error that web2py is generating to confirm that it is the phone number like I think it is.

-Jim

[Cristina Sig]

thanks!

I solved the integer field but it seems that the referenced fields are not working.

I will try to see what is the issue with them

[Jim S]

Are you getting an error on the referenced field (Nationality) or is it just not returning the results you're expecting?

-Jim

[Cristina Sig]

I'm having the same datatables warning: ajax error so I guess it might be the referenced field that is causing the error because even the table is not being field with data.

[Jim Steil]

I think you need to check the web2py errors that are being generated. That should tell you what is going on.

Jim

--
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/q-64a-Qzsqg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/fc8ef221-4178-42bf-80d5-5365bf0eea35%40googlegroups.com.