Perl make a SELECT query and return array of objects

Question

I want to make a query to a sqlite database and return an array of objects.

I'm creating a telegram bot and i have to store the date of last message per user_id, because telegram API returns messages i've already read. So i've created a sqlite db with two tables: users and messages; users contains all the user_id and username; messages contains the last message_id i've read.

I've done this function, it works but it isn't performing very well because it has to recreate the entire array of objects (fetchrow_hashref).

sub sqlLiteSelect {
    my ($db,$table,$columnsRef,$where,$others) = @_;
    my $columns = join(', ', @{ $columnsRef });
    $others ||= '';
    my $query = "select ${columns} from ${table} ${where}${others}";
    my $obj = $db->prepare($query);
    my $ret = $obj->execute() or die $DBI::errstr;
    my @array = ();
    while(my $row = $obj->fetchrow_hashref) {
        my $tmp = {};
        foreach my $column (@{ $columnsRef }) {
            $tmp->{$column} = $row->{$column};
        }
        push @array, $tmp;
    }
    return @array;
}

my @columns  = ('user_id','first_name','username','type');
my $where = ' where user_id in (1,2)';
my @result = sqlLiteSelect($db,'users',\@columns,$where,'');
foreach my $row (@result) {
    print "user_id=$row->{user_id}, username=$row->{username}\n";
}

I expect my select to return an array of object without recreate it everytime.

Why do you copy each column to $tmp instead of directly pushing $row? — sticky bit
– sticky bit, Commented Apr 14, 2019 at 18:12
I thought it could help to understand what I wanted to achieve — Loris
– Loris, Commented Apr 14, 2019 at 18:45
Either use after the execute return fetchall_arrayref({}) ... that will produce the entire list as nice hashrefs, exactly as you have now, but let smart DBI do all the annoying work for you — vanHoesel
– vanHoesel, Commented Apr 14, 2019 at 21:38
Or if you are so much concerned about recreation and speed: change the entire structure. do a prepare, execute, bind_columns(\$user_id, \$first_name, \$username, \$type) and then straight do while ($sth->fetch) { print "$user_id $username\n } forget about the entire subroutine that did create the entire list first — vanHoesel
– vanHoesel, Commented Apr 14, 2019 at 21:45

vanHoesel · Accepted Answer · 2019-04-14 22:11:59Z

2

my ($user_id, $first_name, $username, $type);

my $sql = q{
SELECT user_id, first_name, username, type
  FROM users
 WHERE user_id IN (1,2)
};

my $sth = $db->prepare($sql);
$sth->execute or die $DBI::errstr;
$sth->bind_columns(\$user_id, \$first_name, \$username, \$type);

while ($sth->fetch) {
    print "$user_id, $username\n";
}

read the documentation https://metacpan.org/pod/DBI#bind_columns

answered Apr 14, 2019 at 22:11

vanHoesel

9649 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Loris Over a year ago

First thank you for your replies. I had already saw this way, but as i had already said i was searching for something like [{},{}, ...] if possible. Anyway, from the messages you wrote before, i understand this one is the best option. Thank you for your time. I take this as "accepted answer".

Collectives™ on Stack Overflow

Perl make a SELECT query and return array of objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related