Form the proper DBIx::Class syntax to join a table where the foreign key is stored in a one-to-one join of a different foreign table

Question

I've been reading documentation and trying things, but I can't seem to come up with the syntax I need to create my relationship. I've simplified my need here:

Table: thing  
Field: thingID  
Field: sprocketID  
Field: thingWidth  
(there are more fields)  

Table: sprocket  
Field: sprocketID  
Field: sprocketLength  
(there are more fields)  

Table: cog  
Field: sprocketLength  
Field: thingWidth  
Field: cogPartNum  

I'm trying to model thing.

• Every thing has one sprocket. Multiple things can have the same sprocket, which is why I want to store the sprocketLength (and other information about the sprocket) in the sprocket table and not repeat that in the thing table. More than one sprocket can have the same length.
• Every thing has one cog -- You need the sprocketLength from sprocket AND thingWidth from thing to determine which cog (and thus which cogPartNum).

The following SQL gets me the values I'm looking for:

SELECT * FROM thing
    JOIN sprocket ON thing.sprocketID = sprocket.sprocketID
    JOIN cog ON sprocket.sprocketLength = cog.sprocketLength AND thing.thingWidth = cog.thingWidth
    WHERE thing.thingID = ?;

I've got some code that looks like this, but I'm not sure how to define my cond (the third arg on the cog relationship definition) properly:

My::Schema::Thing->has_one('sprocket' => 'My::Schema::Sprocket', 'sprocketID');
My::Schema::Thing->has_one('cog' => 'My::Schema::Cog', {
    'foreign.thingWidth' => 'self.thingWidth',
    'foreign.sprocketLength' => *??????       # this is where I'm getting confused because sprocketLength isn't in self*
});

So I thought I might need to use a coderef definition for cond instead, but I'm struggling to get that to work. I've got something that looks like this:

My::Schema::Thing->has_one('cog' => 'My::Schema::Cog', sub {
    my $args = shift;
    return {
        "$args->{foreign_alias}.thingWidth" => { -ident => "$args->{self_alias}.thingWidth" },
        "$args->{foreign_alias}.sprocketLength" => $self->{self_result_object}->sprocket->sprocketLength,
    };
});

But that doesn't seem to be working for me. I'm trying to call $self->cog->cogPartNum to access the cogPartNum for a thing in my main code. I'm experiencing multiple executions of the coderef (3 to be exact), the last of which doesn't have a $self->{self_result_object} defined, so I'm getting an error attempting to access undef's sprocket method.

Update: by using some code samples from DBIx::Class::Relationship::Base's perldoc, I've gotten a bit closer, albeit I still don't fully understand it. The second value in the return has to do with the "join-free" case. That seems to work, but if I wanted to use the join case (the first return value) to prefetch my value, the below code still fails.

sub {
    my $args = shift;
 
    return (
        {
            "$args->{foreign_alias}.thingWidth" => { -ident => "$args->{self_alias}.thingWidth" },
            "$args->{foreign_alias}.sprocketLength" => $self->{self_result_object}->sprocket->sprocketLength,   # this is why it fails
        },
        ! $args->{self_result_object} ? () : {
            "$args->{foreign_alias}.thingWidth" => $args->{self_result_object}->thingWidth,
            "$args->{foreign_alias}.sprocketLength" => $args->{self_result_object}->sprocketLength,
        },
        (),
    );
}

Do you have any guidance? Please pardon any typos as I just typed this all out from memory.

Answer 1

Your sprocket and cog relationships should be a belongs_to:

__PACKAGE__->belongs_to('sprocket' => 'My::Schema::Sprocket', 'sprocketID');

To make it work you need to have the sprocket relationship joined. DBIC doesn't provide a way to add a join when a relationship is involved as far as I know.

You're on the right track with -ident, basically what it does is passing its value to the generated SQL so something like this should work if you ensure that the sprocket relationship is joined:

__PACKAGE__->belongs_to('cog' => 'My::Schema::Cog', sub {
    my $args = shift;
    return {
        "$args->{foreign_alias}.thingWidth" => { -ident => "$args->{self_alias}.thingWidth" },
        "$args->{foreign_alias}.sprocketLength" => { -ident => "sprocket.sprocketLength" },
    };
});

Having a method on the ResultSet and/or Result class that does this would be my preferred way of doing it. I'm prefixing all methods that return a ResultSet or array of Result objects with search_.