Prefetch related row or nothing with DBIx::Class, maybe with OUTER LEFT JOIN?

Question

I want to retrieve rows from a table with DBIx::Class and prefetch respective rows from the same table where a column has a particular other value. I need to fetch all assignments from schedule A (to copy them) and retrieve all respective assignments from schedule B.

I have made up tables for testing which look like this:

CREATE TABLE tasks (
    id INTEGER
);

CREATE TABLE schedules (
    id INTEGER
);

CREATE TABLE assignments (
    id INTEGER,
    scheduleId INTEGER,
    taskId INTEGER,
    worker TEXT,
    FOREIGN KEY (scheduleId) REFERENCES schedules(id),
    FOREIGN KEY (taskId) REFERENCES tasks(id)
);

There are some assignments for schedule 1, and a few for schedule 2:

INSERT INTO tasks (id) VALUES (1);
INSERT INTO tasks (id) VALUES (2);
INSERT INTO schedules (id) VALUES (1);
INSERT INTO schedules (id) VALUES (2);
INSERT INTO assignments (id,scheduleId,taskId,worker) VALUES (1,1,1,"Alice");
INSERT INTO assignments (id,scheduleId,taskId,worker) VALUES (2,1,2,"Bob");
INSERT INTO assignments (id,scheduleId,taskId,worker) VALUES (3,2,1,"Charly");

This is some SQL that returns the desired result:

SELECT * FROM assignments AS a1
LEFT OUTER JOIN assignments AS a2 ON
    a2.scheduleId = 2 AND
    a2.taskId = a1.taskId
WHERE a1.scheduleId = 1;

In SQLite this works as expected: The results shows a line for each assignment from schedule 1 and the respective assignment from schedule 2.

id|scheduleId|taskId|worker|id|scheduleId|taskId|worker
1|1|1|Alice|3|2|1|Charly
1|1|2|Bob|NULL|NULL|NULL|NULL

What I've tried with DBIx::Class so far doesn't work. This is what the class for assignments looks like:

package MyApp::Schema::Result::Assignment;

...

__PACKAGE__->has_many(
    inAllSchedules => 'MyApp::Schema::Result::Assignment',
    {
        'foreign.taskId' => 'self.taskId',
    }
);

The following code correctly joins the rows but returns only rows from schedule 1 which actually have a respective row from schedule 2:

my $assignments = $schema->resultset('Assignment')->search({
    'inAllSchedules.scheduleId' => 2,
}, {
    prefetch => 'inAllSchedules',
});

This code correctly also correclty joins the rows and returns rows with no joined row, too, but I don't know how to filter the joined rows. I do not want to retrieve rows for schedule 3 etc. or just any other row ...

my $assignments = $schema->resultset('Assignment')->search(undef, {
    join_type => 'left outer',
    prefetch => 'inAllSchedules',
});

I can not write a specific relationship because the ID of schedule A or B is only given at runtime, of course.

How to either generate the given SQL code or otherwise retrieve the data in a clean way?

Probst

Answer 1

This looks like a case for custom join conditions. The solution below works for your limited example, but may need tweaking for your actual application.

__PACKAGE__->has_many(
    'inAllSchedules' => "MyApp::Schema::Result::Assignment",
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.taskId" => { '-ident' => "$args->{self_alias}.taskId" },
            "$args->{foreign_alias}.id" => { '<>' => { '-ident' => "$args->{self_alias}.id" } },
        };
    }
);

You would use it like so:

my $assignments = $schema->resultset("Assignment")->search({
    'me.scheduleId'             => 1,
    'inAllSchedules.scheduleId' => [ 2, undef ],
},
{
   'prefetch' => "inAllSchedules",
});

Acie

Your answer looks simple and promising but that doesn't work. Comparing the ID to NULL seems to imply the existence of a row. The created SQL reads like SELECT me.*, inAllSchedules.* FROM assignments me LEFT JOIN assignments inAllSchedules ON inAllSchedules.taskId = me.taskId WHERE ( ( ( inAllSchedules.scheduleId IS NULL OR inAllSchedules.scheduleId = 2 ) AND me.scheduleId = 1 ) ). Adding join_type => "left outer" didn't make a difference. – Drumm

Ok, I've marked your answer as accepted because it just works. Also I learned about -ident. Two questions: 1. Is the simple syntax self.column_name and foreign.column_name not valid when returning ON-clauses from a coderef? Why does DBIx::Class place the query arguments in the ON-clause? Actually the condition is defined like before. I don't see what exactly makes the difference and I'd like to understand. Could you please explain that? – Drumm