list:-type fields are stored as strings with items being delimited by the "|" character, so you can do a query that counts the number of pipe characters (minus 1, because the string starts with a "|" character), and select records with a count greater than two. The functions to use may vary depending on the RDBMS, but in SQLite, it would be:
users_gt_2 = "(length(users) - length(replace(users, '|', '')) - 1) > 2" rows = db((db.company.id > 0) & users_gt_2).select() The trick in the users_gt_2 query is to get the length of the "users" field, and then subtract the length after replacing the "|" characters with empty strings (the difference therefore being the number of "|" characters in the original string). The other alternative would be to select all the records and then use Python to do the filtering (you can use rows.find() for that -- http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#find--exclude--sort). Anthony On Monday, April 17, 2017 at 5:26:33 AM UTC-4, Mujeeb Farees wrote: > > I know the auth_user table should have a company field for the case below. > But this is just a sample data, the actual data is different. > > *Company Table* > | *id* | *name* | *users* | > | 1 | Com1 | |1|2| | > | 2 | Com2 | |3|4|5| | > > The users field is of list:reference type that refers to auth_user table. > I need to write a query that will return the names of all companies that > have more than 2 users. Your help will be appreciated. > Thanks! > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
