[Dbix-class] Prefetch Problem: Related data can't be fetched

[Jess Robinson]

On Tue, 26 Feb 2008, sindharta_tanuwijaya at yahoo.co.jp wrote:

> Hi,
>
> I am having a problem, in which I couldn't get the relationship column data by using prefetch.
> My relationship is declared like this, suppose we have two tables: railway and railway_station, which relationship is 1 to many.
>
> #in railway schema
> __PACKAGE__->has_many(
>    "railway_station",
>    "Schema::RailwayStation",
>    { "foreign.railway_station_railway_id" => "self.railway_id" },
> );
>
> #in railway_station schema
> __PACKAGE__->belongs_to(
>  "railway",
>  "Schema::Railway",
>  { "foreign.railway_id" => "self.railway_station_railway_id" },
> );
>
> My prefetch command is as follows:
>
>   my $railways = $schema->resultset('Railway')->search(
>        {
>           'me.railway_id' => { 'IN' => $selected_railway_ids },
>        },
>        {
>            join => 'railway_station',
>            prefetch => 'railway_station',
>        }
>    );
>
> I could get the values of the columns of the railways correctly, but not the columns of railway_stations.

You don't tell us *how* you are trying to get the columns of the railway 
stations, so I really cant tell you where you have gone wrong. Your search 
code is correct.

You can check what SQL it actually runs and try it for yourself, by 
setting DBIC_TRACE=1 in your test script environment.

> If I use 'join' only though, I could get all the data for railways which only have 1 row of railway_station, but not for
> railways which have more than 1 rows of railway_station.
> Any ideas why this happened ?

Sounds like you are accessing the data incorrectly.

> I have a hunch that maybe it's the ID of the railway_station that is causing this problem. In our current database, the primary key of
> railway_station works as a dummy field, in which there are a lot of null values (although they are primary keys).
> Of course, I have my own objections to this current implementation, but I am wondering if that is indeed the problem, since the primary key of railway_station isn't supposed to be used in any query generated above ?

Well, it wont fetch the ones with null relation values, obviously.. but 
would you expect it to?

> Or maybe I am missing something else here.

Like giving us your code? ;)

Jess