Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Populating an array via a DBI call - simplified

by edimusrex (Monk)
on Mar 29, 2017 at 18:05 UTC ( #1186414=perlquestion: print w/replies, xml ) Need Help??

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

This post is more about trying to find a cleaner way to accomplish my task. I use DBI a lot but I am curious if there is a better way to populate and array with the returned value when only a single field is returned. Example ---


SELECT `UserName` FROM Account a, asscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER';

I have always acomplished this by doing the following --

my @regUsers; my $sth = $dbh->prepare("SELECT `UserName` FROM Account a, asscAccount +AccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaa +g.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.Ro +leId = r.Id AND r.Name = 'ROLE_REVIEWER';") || die "Cannot prepare th +e query :$!"; $sth->execute() || die "Cannot execute the query :$!"; while ( my @userRows = $sth->fetchrow_array() ) { push @regUsers, @use +rRows; } print join("\n",@regUsers);
Which always works as expected but I'd like to simplify that code if possibe.

Doing the following seems to return a list of references but is close --

my @users = $dbh->selectall_array("SELECT `UserName` FROM Account a, a +sscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE +a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId A +ND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER'"); print join("\n",@users);

Thanks for the suggestions

Replies are listed 'Best First'.
Re: Populating an array via a DBI call - simplified
by Your Mother (Bishop) on Mar 29, 2017 at 18:07 UTC

    This is a great chance to bump one of the most classic nodes here: DBI recipes.

      Great thread. Seems the answer to my question is as followed ---

      my @accounts = @{$dbh->selectcol_arrayref("SELECT `UserName` FROM Acco +unt a, asscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role +r WHERE a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountG +roupId AND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER'")}; print join("\n",@accounts);

      Thanks again for pointing me in the right direction

        If you don't really need the entire list of results in an array, you can just fetch one at a time like so:
        my $sth = $dbh->prepare($sql); $sth->execute(); $sth->bind_col( 1, \my $user_name ); while ( $sth->fetch() ) { print "$user_name\n"; }
      Great! Thanks
Re: Populating an array via a DBI call - simplified
by Discipulus (Abbot) on Mar 30, 2017 at 08:23 UTC
Re: Populating an array via a DBI call - simplified
by ablanke (Priest) on Apr 01, 2017 at 10:11 UTC

      Slightly [OT]...

      my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;

      I thought that looked nice so I had a look at it. But when compared to the 'normal' DBI way it turns out there is really not much of an advantage. The line is a little shorter (++) but you get a dependency (--) and lose quite a bit of speed (--).

      perl -MData::Dumper -MDBI -MDBIx::Simple -e ' my $db1 = DBI->connect; # (uses defaults to connect) my $db2 = DBIx::Simple->connect; use Benchmark qw( cmpthese ) ; my $COUNT = -20; print "-- COUNT [$COUNT]\n"; cmpthese( $COUNT, { selectrow_arrayref => sub { my @arr = @{ $db1->selectrow_arrayref( + "select i from generate_series(1,10) as f(i) where i > 5" ) }; }, dbix_query_flat => sub { my @arr = $db2->query( "select i from +generate_series(1,10) as f(i) where i > 5")->flat; }, }); ' -- COUNT [-20] Rate dbix_query_flat selectrow_arrayref dbix_query_flat 5608/s -- -23% selectrow_arrayref 7293/s 30% --

      As always when I look closer at ORMy things (mainly because many people seem to be fond of ORMs), I don't think it's worth it.

        Hi erix,

        first of all ++ for your post. That are the choices you have to made.

        Slightly OT...

        Yes, i tried to see the OP from a different angle.

        As always when I look closer at ORMy things...

        I don't think it's too ORMy.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2019-05-20 08:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you enjoy 3D movies?



    Results (125 votes). Check out past polls.

    Notices?