CODEWITHSUNDEEP
perldbix-class
extmkv
extmkv

Reputation: 2045

DBIx::Class Get row from another table by ID

In my project I have 3 tables: Artists, Albums and Tracks

Result Artist:

...
__PACKAGE__->has_many(
     'albums' => 'MYLIB::DB::Schema::Result::MyDir::Album',
     { 'foreign.artist_id' => 'self.id', },
);
...

Result Album:

...
__PACKAGE__->belongs_to(
     'artist' => 'MYLIB::DB::Schema::Result::Artist',
     { 'foreign.id' => 'self.artist_id', },
  );

__PACKAGE__->has_many(
     'tracks' => 'MYLIB::DB::Schema::Result::MyDir::Track',
     { 'foreign.album_id' => 'self.id', },
 );
...

Result Track:

__PACKAGE__->belongs_to(
    'album' => 'MYLIB::DB::Schema::Result::MyDir::Album',
    { 'foreign.id' => 'self.album_id', },
);

Now I have a object $artist and I want to get a Track by ID.

Query Example: SELECT * FROM Tracks WHERE track_id = $x

Upvotes: 3

Views: 322

Answers (2)

Dave Cross
Dave Cross

Reputation: 69264

If you want to generate the SQL that you give us, then the fact that you have an artist object is irrelevant. Just get a track resultset and run find() on that.

my $track_rs = $schema->resultset('Track');
my $track = $track_rs->find($track_id);

If, for some reason, you don't have a schema object, then you can get that from your artist object.

my $schema = $artist->result_source->schema;

Upvotes: 6

BarneySchmale
BarneySchmale

Reputation: 780

I assume that the track is on an album by your $artist. The query can be done by a join over the three tables. See DBIx::Class::Manual::Joining.

Here is an untested example.

my $tracks = $artist->search_related(
    { 
        id => $my_track_id,
    },
    { 
        join => { albums => 'tracks' },
    }
);

If your track is not necessarily by $artist, then it propably makes more sense to query tracksdirectly.

Upvotes: 1

Related Questions