Hi,
My friend solved the problem. It's weird though and I am still a little bit
confused. The way I am trying to access/display the rows is like this:
[% FOREACH railway = railways -%]
[% railway.railway_station.railway_station_fields %]
[% END -%]
And as Nigel pointed out,
> Single object which returns an array from the has_many accessor.
If I use 'join' only, I could get all the data for railways which only have 1
row of railway_station by using "railway.railway_station", but for railways
which have more than 1 rows of railway_station, I had to use something like
"railway.railway_station.0". But it always failed for "prefetch"
The way I wanted it, is that, since my search code is like this,
my $railways = $schema->resultset('Railway')->search(
{
'me.railway_id' => { 'IN' => $selected_railway_ids },
'railway_station.main_flag' => 1, ## forgot to mention this earlier
},
{
join => 'railway_station',
prefetch => 'railway_station',
}
);
$c->stash->{railways} = [$railways->all];
And although railway has 'has_many' rel, with the condition main_flag=1, only 1
or null railway_station should be returned for each railway, and therefore, I
am hoping to access it by [% railway.railway_station%]
The way that my friend solved it, was to declare a select variable
my @select = (
qw/
railway_field_1
railway_field_2
main_flag #from railway_station
railway_station_field1 #from railway_station
/
);
and put it in the search condition like this:
my $railways = $schema->resultset('Railway')->search(
{
'me.railway_id' => { 'IN' => $selected_railway_ids },
'railway_station.main_flag' => 1, ## forgot to mention this earlier
},
{
select => [EMAIL PROTECTED],
join => 'railway_station',
}
);
and he wasn't using prefetch at all. Now, I could access 'main flag' field by
using [% railway.main_flag.%], instead of
[% railway.railway_station.main_flag %]
Any ideas why this happened?
Sindharta
Jess Robinson <[EMAIL PROTECTED]> wrote:
On Tue, 26 Feb 2008, [EMAIL PROTECTED] 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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
---------------------------------
Easy + Joy + Powerful = Yahoo! Bookmarks x Toolbar
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
