Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

DBIx::Class : match integer-cols having specific bits set

by bliako (Prior)
on Sep 28, 2020 at 18:26 UTC ( #11122300=perlquestion: print w/replies, xml ) Need Help??

bliako has asked for the wisdom of the Perl Monks concerning the following question:

Enlightened Brothers,

I am having bit (!) trouble with telling DBIx::Class to match records where a specific column of integer type (actually BIGINT) has specific bit(s) set. The equivalent SQL is something like: select * from TABLE where (roles & 4) = 1 (Edit: the RHS of this where expression, i.e.: =1 was written rather hastily and it does not make sense for checking if the 2nd LSbit is set. roles=4 is much clearer and probably faster. Instead,  (roles&4) = 4, (roles&4) > 0. (roles&4)=0 make more sense in my particular scenario)

Since we are on the topic, I have not yet understood how to use the column-name in a search with DBIx::Class. For example how to DBIx::Class this: select * from TABLE where (roles & 4) = (roles & 2) ?

I can also settle for someone telling me how to pass a custom WHERE-in-SQL using DBIx::Class::ResultSet::search(), if possible.

Oh! MySQL latest

bw, bliako

Replies are listed 'Best First'.
Re: DBIx::Class : match integer-cols having specific bits set
by 1nickt (Abbot) on Sep 28, 2020 at 19:51 UTC

    Greetings brother bliako,

    Generally, to signify that the RHS in a where subclause is a column identifier, use -ident.

    my %where = ( foo => 'bar', baz => { -ident => 'qux' }, );
    produces
    where foo = "bar" and baz = qux

    Also note that you can supply literal SQL using a reference to a string.

    Sorry, IDK about your bitwise issue.

    See the doc for SQL::Abstract, which DBIx uses behind the scenes.

    Hope this helps!


    The way forward always starts with a minimal test.

      Brother 1nickt thanks for the hint and the pointers to SQL::Abstract which helped me to put together the literal SQL queries below:

      my %where = ( '(roles &2) => \'(roles &4)', );

      and

      my %where = ( 0 => {'<' => \"(roles & 4)"}, );

      or

      my %where = ( '(roles & 4)' => {'>' => '0'}, );

      bw, bliako

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11122300]
Approved by Paladin
Front-paged by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (2)
As of 2020-11-30 04:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?