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?
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",
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With