Thank you for answering, this worked for me. I want to avoid loops after query, so I used *users_gt_2* approach.
On Monday, April 17, 2017 at 7:10:38 PM UTC+5, Anthony wrote: > > 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.
