in reply to Re: Avoiding SQL double jeopardy
in thread Avoiding SQL double jeopardy

Thanks! Adding a table does the trick at the cost of some reworking of adds and fetches. Is this the SQL equivalent of "Add another level of indirection"?

Reworked sample is:

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,
    DoneDate TEXT,
    Topic TEXT
    )
SQL
$dbh->do(<<SQL);
CREATE TABLE SessPeople (
    SessId INTEGER,
    PersonId INTEGER,
    IsTeacher INTEGER
    )
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 for fun and proffit', '2015-06-20')
+;

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

print "$_->[1], $_->[2]: '$_->[3]'\n" for @$active;


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

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

    my $sessId = $self->{dbh}->last_insert_id(undef, undef, undef, und
+ef);

    $self->{dbh}->do(<<SQL, {}, $sessId, $teacher);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 1)
SQL
    $self->{dbh}->do(<<SQL, {}, $sessId, $learner);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 0)
SQL
}


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

    return $self->{dbh}->selectall_arrayref(<<SQL, {}, @ids);
SELECT DISTINCT s.SessId, t.PersonId, l.PersonId, s.Topic FROM Session
+s s
    JOIN SessPeople t ON t.SessId == s.SessId AND t.IsTeacher
    JOIN SessPeople l ON l.SessId == s.SessId AND NOT l.IsTeacher
    JOIN SessPeople p ON p.SessId == s.SessId
    WHERE p.PersonId IN ($idPlaces)
SQL
}

[download]
Perl is the programming world's equivalent of English

Replies are listed 'Best First'.
Re^3: Avoiding SQL double jeopardy
by Pope-O-Matik (Pilgrim) on Jun 28, 2015 at 03:55 UTC

    I'm not sure what you're trying to do here, but whatever it is, this is overcomplicating it.

    Aside from that, you're using dynamic SQL and a flag to determine if the person is a teacher or not. Use a Role id instead. That's what you want. The flag is a trick, and, it may bite you in the future.

    Can more than one person be in the same session? Why does it matter if the person is a teacher or student?

    It looks like you're inserting each person twice, once as a teacher and once as a student. If so, it can be done in one query:

    INSERT INTO SessPeople (SessId, PersonId, IsTeacher) SELECT ?, ? 0 UNION ALL SELECT ?, ? 1;

    The second query is daunting. I have no idea what you're trying to do. If the original query (topic starting post) table was used, you can check via many ways. The easiest is to connect the two columns via UNION ALL:

    WITH Session2 AS(SELECT AS (SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions UNION ALL SELECT SessId, LearnerId, DoneDate, Topic FROM Sessions) SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions2 WHERE TeacherId IN ();

    The rewritten query here might be more complicated, but it does't look right. Could you explain a little more clearly what you;re trying to achieve? I have a feeling the solution is considerably simpler.

[reply]
[d/l]
[select]

      The big picture is we have a weekly "experience exchange" in our development team. Each week we offer a topic we are prepared to teach for 3/4 of an hour. Then at the appointed time we pair up as teacher/learner for our weekly session. Over time it is expected that we spend about as many sessions teaching as learning.

      At present the process is mediated by post it notes stuck to a wall. The script is intended to replace the post it note scrum and help ensure everyone gets a more or less equal opportunity to teach and learn.

      So, a person can only be in each session once. For any session a person may be a teacher or a learner. There will only ever be teacher and learner roles. Each session includes everyone who is available.

      The query at the heart of the issue is to find all pending session pairings an individual or group of individuals are involved in.

      Perl is the programming world's equivalent of English
[reply]

        Thank you, i think i got it now. :) Two more clarifications, though: Can there be more than one teacher per session? Can there be more than one student per session? That is, "by definition" as opposed to how it just happens to work out.

[reply]

In Section Seekers of Perl Wisdom