CassJ has asked for the wisdom of the Perl Monks concerning the following question:

hello monks

I've just started using Class::DBI and I can't quite get the hang of setting up relationships. I think I'm probably missing something obvious here, but I can't figure out what's going on. Any help would be appreciated!

I have a class Sessions_Metadata which contains foreign keys to classes Users and Sessions (see below). I have a test script which creates an instance of Sessions_Metadata and attempts the following tests: [1] isa_ok($sessions_metadata->username,"EP::Common::DBI::Users");[2] isa_ok($sessions_metadata->session_id,"EP::Common::DBI::Sessions"); If I only run test 2, it works fine and I get:
ok 1 - The object isa EP::Common::DBI::Sessions
If I run both tests, or I run only test 1 I get an error:
Can't bind a reference (EP::Common::DBI::Sessions=HASH(0x85d1d70)) at /usr/local/lib/perl5/site_perl/5.8.1/DBIx/ContextualFetch.pm line 51,<FILE> line 47.

So, I thought the problem was with test 1 and the Users class (even though the error refers to Sessions), but if I comment out the has_a(session_id=>'EP::Common::DBI::Sessions') line in the Sessions_Metadata class, and run test 1 I get:
ok 1 - The object isa EP::Common::DBI::Users

So, if I have both has_a definitions, the Sessions class works, but the Users class doesn't. If I only have the has_a defined for Users then the Users class works.

Can anyone see what I've done wrong?

Many thanks

Cxx

Class Definitions... 

###### Users ####### 
package EP::Common::DBI::Users; 
use strict; 
use warnings; 
use base qw( EP::Common::DBI ); 
__PACKAGE__->table('users');
__PACKAGE__->columns(Primary => qw/username/); 
__PACKAGE__->columns(All => qw/password salt session_only first_name 
last_name institute department address telephone email/);


###### Sessions ########
package EP::Common::DBI::Sessions;
use strict;
use warnings;
use base qw( EP::Common::DBI );
__PACKAGE__->table('sessions');
__PACKAGE__->columns(Primary => qw/id/);
__PACKAGE__->columns(All => qw/a_session/);


######### Sessions_Metadata #################
package EP::Common::DBI::Sessions_Metadata;
use strict;
use warnings;
use base qw( EP::Common::DBI );
#setup table fields
__PACKAGE__->table('sessions_metadata');
__PACKAGE__->columns(Primary => qw/session_id/);
__PACKAGE__->columns(Others => qw/create_date username expire query_id
+/);
#relations
__PACKAGE__->has_a(username=>'EP::Common::DBI::Users');
__PACKAGE__->has_a(session_id=>'EP::Common::DBI::Sessions');

[download]

Replies are listed 'Best First'.
Re: Class::DBI has_a relationships
by jeffa (Bishop) on May 18, 2004 at 16:24 UTC

    I doubt this will fix the error, but shouldn't your Sessions_Metadata cross reference table have two primary keys? 

    __PACKAGE__->columns(Primary => qw/session_id username/);
__PACKAGE__->columns(Others => qw/create_date expire query_id/);

    [download]
    Hope this helps ...

    UPDATE:
    Ahhh ... i see the problem now. You are not setting up your Users and Sessions classes to be able to 'have many' Sessions_Metadata. Here is how you might set up Users (untested). I am not sure how your tables really relate, so you may or may not need to do something similar in Sessions. Here goes: 

    __PACKAGE__->has_many(
   sessions => [EP::Common::DBI::Sessions_Metadata => 'id'] 
            => 'session_id'
);

    [download]
    You might need to explain more to us about how these tables relate to one another in order to find the solution.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
      The CDBI Wiki has an entry about setting up many-to-many relationships, along with an example.

      Please contribute whatever you may learn back to the wiki so others can learn from your efforts.

[reply]
      Hi, thanks for the help. Can you explain what
      __PACKAGE__->has_many(
   sessions => [EP::Common::DBI::Sessions_Metadata => 'id'] 
            => 'session_id'
);

      [download]
      is doing?

      According to the Class::DBI perldoc, has_many works like:
      has_many(method_to_create=>"foreign_class");
      so I thought I needed something like: __PACKAGE__->has_many(sessions_metadata=>"EP::Common::DB::Sessions::Metadata");
      in both Users and Sessions to declare that Sessions_Metadata was referencing them. But I get the same 'can't bind a ref error'.

[reply]
[d/l]
[select]

        That (what you have) declares that a User has many Sessions::Metadata and that a Session has many Sessions::Metadata, but you also have to tell Sessions::Metadata that it references Users and Sessions as well.

        My snippet in question is specifying that a User can have many Sessions::Metadata classes, and the Users' foreign key 'session_id' references the Sessions::Metadata primary key 'id'. Now, whether or not this is the right fit for your database table relationship is the question, and since i don't know what it looks like, i can only speculate.

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
Re: Class::DBI has_a relationships
by perrin (Chancellor) on May 18, 2004 at 18:01 UTC
    The Class::DBI docs contain the following warning:

    *NOTE* You should not attempt to make your primary key column inflate using has_a() as bad things will happen. If you have two tables which share a primary key, consider using might_have() instead.

    You can use might_have in your Sessions_Metadata class and that should do the trick. Check the Class::DBI docs for the syntax.

[reply]
      aaaah.

      Thanks.

      I knew I was missing something.

      So if I follow jeffa's suggestion of merging the sessions and sessions_metadata tables so that I have a single sessions table with a (non-primary-key) foreign key to the users table then all should be fluffy and I can stop banging my head off the desk...?

      ...or I could use might_have, which works fine. Sigh. that'll teach me to read docs properly...

      Cheers guys,

      Cxx

[reply]
      FYI, there was a patch submitted to the list to take care of this problem. It will be interesting to see if it passes muster and makes it into the next release. Apparently PK's are not inflated/deflated when relationships are setup on them. This causes problems when going to/from the database after updates.

      The patch: http://groups.kasei.com/mail/arc/cdbi-talk/2004-04/msg00200.html

[reply]
        Thanks.

        Tried the patch and am still getting the error, but will post details to the mailing list and report back if I get a solution

        Cheers, Cxx.

[reply]

Back to Seekers of Perl Wisdom