Searching by bitmask in ActiveRecord

Question

I have a users table with a bitmask field that has a permissions mask in it. Locally, I can determine whether a user has a certain permission by doing a bitmask (UserPermissions&Perm)==Perm. However, I want to be able to issue a find_by_mask or something similar, perhaps using a :conditions, but I can't seem to find out how I can query the database to retrieve a list of users with a matching permission mask.

Any ideas using ActiveRecord?

Specifically this must work using sqlite and postgres

Answer 1

The sane thing to do, in my opinion, would be to break out your bitmask field into a series of boolean fields. Storing bitmasks in a relational database is not that far off from storing delimited lists in a field -- it's borderline denormalization.

That said, you can use a bitwise AND in your SQL queries with the & operator, so you can say:

User.where('permissions & ? > 0', Perm)