Query auth_user over first and last names in one query

[Jim S]

Hi

I have a search form where the user types in generic search text.  I want to be able to return to them a list of matching users.

Sample Data

  First       Last
- ----------  ----------
1 Jim         Sanders
2 Bill        Van Der Wall
3 John        St James
4 Peter       Williams
5 Jim         Hensen
6 John        Adams
7 William     Tell
8 Adam        Johnson
 

Based on the data entered in the search box, these records should be returned

  
  Search Text      Rows Returned

- ---------------- ------------------
1 Jim              1, 5
2 John             3, 6, 8
3 Adam             6, 8
4 Bill             2
5 Jim Sanders      1
6 Adam John        8
7 John St James    3
 

I can't seem to come up with a query or anything to make this happen.  With SQL I might to this:

'SELECT * FROM auth_user WHERE first_name LIKE \'%s*\' OR last_name LIKE \'%s*\' OR concat(first_name, ' ', last_name) LIKE \'%s*\'' % (search_text, search_text, search_text)

But, I want to build this as a query for SQLFORM.grid.  

Anyone have any ideas?

-Jim

[Jim S]

Bump - didn't get any takers the first time.   Wondering if anyone has any ideas...

-Jim

[Áureo Dias Neto]

name = form.vars.name

lastname = form.vars.lastname

query = db((db.auth_user == name) or (db.auth_user == lastname)).select()

form = SQLFORM.grid(query)

[Áureo Dias Neto]

or this:

name = form.vars.name

lastname = form.vars.lastname

query = ((db.auth_user == name) or (db.auth_user == lastname))

form = SQLFORM.grid(query)

[Jim S]

That will work, but I'm only wanting 1 form field.  Basically want to implement a CONCAT function like in the sample SQL Statement I mentioned above.

-Jim

[Áureo Dias Neto]

you can use the 'like' condition in w2p, something like this:

query = ((db.auth_user.like('%s%' form.var.name) or (db.auth_user.like('%s%' form.var.name))))

--

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jim S]

That doesn't work because if I put in 'Tom Jones' into my search it doesn't match either first name or last name.  Only matches if I concatenate the first and last name fields together.

-Jim

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Anthony]

On Thursday, March 9, 2017 at 9:05:27 AM UTC-5, Áureo Dias Neto wrote:

you can use the 'like' condition in w2p, something like this:

query = ((db.auth_user.like('%s%' form.var.name) or (db.auth_user.like('%s%' form.var.name))))

You cannot build a query from the table object alone -- you must specify a field (e.g., db.auth_user.first_name rather than just db.auth_user).

Anthony

[Anthony]

I haven't tried it, but maybe something like this:

    def search(sfields, keywords):
        keywords = keywords.strip().replace("'", "''")
        return "CONCAT(first_name, ' ', last_name) LIKE '%%%s%%'" % keywords

    grid = SQLFORM.grid(db.mytable, searchable=search)

Actually, although not documented as part of the public API, each adapter has a CONCAT method, which produces the correct syntax for each database. So, the last line of the search function above could be:

        return "%s LIKE '%%%s%%'" % (db._adapter.CONCAT('first_name', ' ', 'last_name'), keywords)

Anthony

[Jim Steil]

Holy Cow!  I got it working.

Not exactly what you specified Anthony, but got me on the right track

With your method I got this traceback:

Traceback (most recent call last):
  File "C:\dev\web2py\gluon\restricted.py", line 216, in restricted
    exec(ccode, environment)
  File "C:\dev\web2py\applications\connect\controllers/user.py:index", line 281, in <module>
  File "C:\dev\web2py\gluon\globals.py", line 405, in <lambda>
    self._caller = lambda f: f()
  File "C:\dev\web2py\gluon\tools.py", line 4299, in f
    return action(*a, **b)
  File "C:\dev\web2py\applications\connect\controllers/user.py:index", line 72, in index
AttributeError: 'MySQL' object has no attribute 'CONCAT'

However, I then tried this:

queries = [db.auth_user.id > 0]

queries.append((db.auth_user.firstLast.contains(searchText)) |

  (db.auth_user.lastFirst.contains(searchText)) |

  ("CONCAT(first_name, \' \', last_Name) LIKE '%%%s%%'" % (searchText)))

query = reduce(lambda a, b: (a & b), queries)

...and passed the query and it worked.

Much appreciated!

-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/3RnGSoUbxxY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Anthony]

What did your code look like, and are you using an older version of web2py?

[Jim Steil]

Here is my version info:

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2017

Version 2.14.6-stable+timestamp.2016.05.09.19.18.48

Database drivers available: pymysql, imaplib, MySQLdb, sqlite3, pg8000, pyodbc

Code used to be:

queries = [db.auth_user.id > 0]

queries.append((db.auth_user.firstLast.contains(searchText)) | (db.auth_user.lastFirst.contains(searchText)))

query = reduce(lambda a, b: (a & b), queries)

I'd hadn't figured out that I could pass text like that to my sub-query.

[Richard Vézina]

single_input_first_last_name.split(' ')[0] single_input_first_last_name.split(' ')[1] can workin case your user input first and last name in a proper order or you search both first and last name 4 times if you don't know in which order you get them with OR ('|') you will get everythings out you have in the database....

Richard

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+unsubscribe@googlegroups.com.

[Richard Vézina]

You may need also to make some cleansing for multiple white spaces with replace before your final split and add some logic for middle name and add further where clause condition...

where_clauses = []

where_clauses.append((db[request.args(0)].fieldname <= request.vars.input_entry))

query = reduce(lambda a, b: a | b, where_clauses)

rows = db(query).select(*output_columns)

But your contains() solution may be simpler...

Richard

[Anthony]

I meant what was the code that produced that CONCAT exception?

[Jim Steil]

queries = [db.auth_user.id > 0]

queries.append("%s LIKE '%%%s%%'" % (db._adapter.CONCAT('first_name', ' ', 'last_name'), searchText)

query = reduce(lambda a, b: (a & b), queries)

-Jim

On Thu, Mar 9, 2017 at 4:03 PM, Anthony <abas...@gmail.com> wrote:

I meant what was the code that produced that CONCAT exception?

[isi_jca]

Jim:

In the next expression

query = db((db.auth_user == name) or (db.auth_user == lastname)).select()

Replace "or" by "|" (pipe)

query = db((db.auth_user == name) | (db.auth_user == lastname)).select()

Regards.

[Anthony]

On Thursday, March 9, 2017 at 5:08:56 PM UTC-5, Jim S wrote:

queries = [db.auth_user.id > 0]

queries.append("%s LIKE '%%%s%%'" % (db._adapter.CONCAT('first_name', ' ', 'last_name'), searchText)

I guess you must have pulled a more recent version version of web2py from Github (i.e., after the last stable release) -- looks like pydal was refactored and CONCAT is no longer there.

Anthony

[Jim Steil]

That is possible. 

Jim

--