Storing flags in a SQL database and working with them in Symfony

I have a set of objects in a DB which can have a number of boolean flags associated with them.

The flags will be pre-defined but may flags may be added or removed later. I could store them in a table with FlagID and FlagName.

Storing the values of these flags is easy - they could be saved in a simple table containing the ObjectID and the FlagID - an entry in this table would indicate a 'set' flag.

If I then did a query with a join, it would be easy to extract the Objects with their 'set' flags.

But my Symfony application (using Doctrine as the ORM) needs to get all of the 'unset' values in this join so it can offer checkboxes for setting them - i.e the ideal output would be

ObjectID FlagID Value
1        1      True
1        2      False
2        1      False
2        2      False
3        1      False
3        2      True

This result set would result from the following data in the database

ObjectID FlagID
1        1
3        2


FlagID   FlagName
1        Foo
2        Bar

This way, I don't need to store all the unset flags against each Object, and thus I don't need to worry about pre-populating the table with unset flags whenev开发者_运维知识库er a flag is added.

Is there a query that will generate this resultset?

You need something like so:

select object.id,
       flags.id,
       object_flags.flag_id is null as has_flag
from objects
cross join flags
left join object_flags
on object_flags.object_id = objects.id
and object_flags.flag_id = flags.id

another solution would be to use a 8byte integer which would give you upto 64 flags, that you could fetch at once. it will eliminate the hassle of fetching multiple records and read every field.

Refer Most efficient way to extract bit flags

Best practices for bit flags in PHP