6

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

Mitch Dempsey
  • 38,725
  • 6
  • 68
  • 74

1 Answers1

17

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)
Seamus Campbell
  • 17,816
  • 3
  • 52
  • 60
  • 7
    Thanks for the bitwise info. I thought about using booleans, but I have some 15 permissions, and might add more later. Using a bitmask is much much cleaner than having 15+ booleans in the table. – Mitch Dempsey Aug 02 '10 at 04:18