Use length of list:reference field in query

[Mujeeb Farees]

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! 

[Anthony]

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

[Mujeeb Farees]

Thank you for answering, this worked for me. I want to avoid loops after query, so I used users_gt_2 approach.

[Sharjeel Ali Shaukat]

Its help me alot Thanks