Rose::DB masking in query statements

Question

I'm seeking some help on my Rose::DB issue as described below.

I have an account object, and this has an integer "accounttype" field. In this example I will use the following accounttype constants:

ACCOUNT_TYPE_SPECIAL_MASK   = 0x10;
ACCOUNT_TYPE_SPECIAL_1      = 0x10;
ACCOUNT_TYPE_SPECIAL_2      = 0x11;
ACCOUNT_TYPE_NORMAL_MASK    = 0x20;
ACCOUNT_TYPE_NORMAL_1       = 0x20;
ACCOUNT_TYPE_NORMAL_2       = 0x21;

At present, when I want accounts of a given type, I'd list them all and do something like this:

my $iter    = Test::Account::Manager->get_accounts_iterator(
    db              => $db,
    query           =>
    [
        'accounttype'   => [ ACCOUNT_TYPE_SPECIAL_1, ACCOUNT_TYPE_SPECIAL_2 ],
    ]
);

However, I'd like to be able to query for the accounts using the appropriate mask, rather than specify all possible types.

I'd like to be able to say:

my $iter    = Test::Account::Manager->get_accounts_iterator(
    db              => $db,
    query           =>
    [
        'accounttype'   => 'accounttype & ACCOUNT_TYPE_SPECIAL_MASK'
    ]
);

However, I haven't spotted any way to do this. Any help or recommendations most welcome.

Thanks!

Answer 1

Let's say your SQL server understands the following:

(accounttype & 16) <> 0

This would then suggest that you could use the following:

[ \'(accounttype & ?) <> 0' => ACCOUNT_TYPE_SPECIAL_MASK ]

Under the circumstances, you could inline the constant.

\sprintf('(accounttype & %d) <> 0', ACCOUNT_TYPE_SPECIAL_MASK)

I don't know if these two versions result in different SQL, and I don't know which is faster if so.