As part of a scheduling project I need to retrieve "active sessions" for selected people. For each session a person may be the teacher or learner. I have code (see getActiveSessions) that retrieves the data I want from the Sessions table in a SQLite database. However the duplication if the id list to be matched smells somewhat to me. Can you come up with a tidy alternative that avoids duplicating the id list?

A test script that reflects the current code is given below:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=xr.SQLite');

$dbh->do(<<SQL);
CREATE TABLE Sessions (
    SessId INTEGER PRIMARY KEY AUTOINCREMENT,
    TeacherId INTEGER,
    LearnerId INTEGER,
    DoneDate TEXT,
    Topic TEXT
    )
SQL

my $self = bless {dbh => $dbh};

$self->addSession(1, 2, 'Perl is fun',            '2015-06-27');
$self->addSession(3, 4, 'SQL is fun',             '2015-06-27');
$self->addSession(3, 5, 'SQL for fun and profit', '2015-06-20');
$self->addSession(4, 1, 'PerlMonks is great',     '2015-06-20');
$self->addSession(2, 6, 'PerlMonks is great',     '2015-06-20');

my $active = $self->getActiveSessions(1, 2);

print "$_->{TeacherId}, $_->{LearnerId}: '$_->{Topic}'\n"
    for values %$active;


sub addSession {
    my ($self, $teacher, $learner, $topic) = @_;

    $self->{dbh}->do(<<SQL, {}, $teacher, $learner, $topic);
INSERT INTO Sessions (TeacherId, LearnerId, Topic, DoneDate)
    VALUES (?, ?, ?, '')
SQL
}


sub getActiveSessions {
    my ($self, @ids) = @_;
    my $idPlaces = (join ', ', ('?') x @ids) || '';

    $idPlaces = <<SQL if $idPlaces;
 AND (TeacherId IN ($idPlaces) OR LearnerId IN ($idPlaces))
SQL

    return $self->{dbh}->selectall_hashref(<<SQL, 'SessId', {}, @ids, 
+@ids);
SELECT SessId, TeacherId, LearnerId, Topic FROM Sessions
    WHERE DoneDate == ''$idPlaces
SQL
}
[download]

Prints:

4, 1: 'PerlMonks is great'
1, 2: 'Perl is fun'
2, 6: 'PerlMonks is great'
[download]