[Dbix-class] Fixing my relationships to get working queries

[John Stoffel]

>>>>> "Hartmaier" == Hartmaier Alexander <alexander.hartmaier at t-systems.at> writes:

Hartmaier> On 2015-01-19 18:41, John Stoffel wrote:
>>>>>>> "Peter" == Peter Rabbitson <rabbit+dbic at rabbit.us> writes:
Peter> On 01/13/2015 03:33 AM, John Stoffel wrote:
>>>> # search in Carey
>>>> my @r = $schema->resultset('Name')->search( { full_name =>
>>>> { regexp => '[[:<:]]'.$query.'[[:>:]]' }
>>>> },
>>>> {
>>>> order_by => { -asc => 'full_name' },
>>>> prefetch => { 'account' => 'boxfolder' },
>>>> rows => $limit,
>>>> });
>>>> return @r;
>>>> 
>>>> 
>>>> 
>>>> And I don't get back consistent the info I expect.  Sometimes it works
>>>> and I get what I think is the right info, but other times for other
>>>> queries it doesn't give me back what I want.
Peter> You need to expand on what precisely isn't "as expected". That
Peter> is - from your query above I can see DBIC generating the SQL to
Peter> satisfy exactly what you asked for. So the disconnect is in you
Peter> ot fully understanding the way you formulated the "query" to
Peter> DBIC itself.
>> 
>> Sorry, I'm expecting to get back consistent results.  I've actually
>> given up trying to make multi-step join or prefetch work for me,
>> because I was running out of time.  So I ended up de-normalizing my
>> data.
>> 
>> Just to refresh the conversation, I have a table with the following
>> two level relationship:
>> 
>> Names -> has_many -> Accounts -> has_one -> Boxfolder
>> 
>> Where many different accounts can share a Boxfolder row.  Basic
>> stuff.

Hartmaier> Seems like one of the more often happening mistakes: the
Hartmaier> has_one should be a belongs_to instead because Accounts
Hartmaier> stores the primary key of a Boxfolder.

It is a mistake on my part, but only in the above section.  I think I
have it correctly setup in the __PACKAGE section of Boxfolder.pm, as
shown below:

>> Boxfolder.pm:
>> package Carey::Schema::Result::Boxfolder;
>> use base 'DBIx::Class::Core';
__PACKAGE__-> table("boxfolder");
__PACKAGE__-> add_columns(
>> "boxfolder_id",
>> { data_type => "integer", is_auto_increment => 1, is_nullable => 0
>> },
>> "volume",
>> { data_type => "varchar", is_nullable => 0, size => 20 },
>> "box",
>> { data_type => "varchar", is_nullable => 0, size => 10 },
>> "folder",
>> { data_type => "varchar", is_nullable => 1, size => 20 },
>> "range",
>> { data_type => "text", is_nullable => 1 },
>> "comments",
>> { data_type => "varchar", is_nullable => 1, size => 100 },
>> );
__PACKAGE__-> set_primary_key("boxfolder_id");
__PACKAGE__-> belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');
>> 


So I'm stumped honestly.  Something I'm doing is incorrect here, but I
can't figure it out.  I'll poke at this more later.

John