[CDBI] Re: Class::DBI and M:M

Thanks a lot. That's exactly what I was looking for.


On 3/19/07, Edward J. Sabol <sabol-2oVx0MVsMgiP/[email protected]> wrote:
>
> > I have a table "contact" and another table "link" which has
> > 2 foreign keys to "contact" - contact_id_1 and contact_id_2. This
> > is essentially a many-to-many relationship, but with only 2 tables.
>
> I'm a little confused as to the schema and the relationships. I presume
> the
> "link" table has a primary key "link_id"? Or is it a multi-column primary
> key
> consisting of contact_id_1 and contact_id_2?
>
> > I would like to know how to handle this with Class::DBI.
>
> For the most part, this sounds like multiple has_a() relationships:
>
> http://search.cpan.org/~tmtm/Class-DBI-v3.0.15/lib/Class/DBI.pm#has_a
>
> But I think you'll end up writing your own look-up methods, such as the
> code
> I've included below.
>
> > I would also like to know how to extend Class::DBI to allow me to search
> > like this:
> >
> > select contact_id_1 from link where contact_id_2 = ?
> > union
> > select contact_id_2 from link where contact_id_1 = ?
>
> Refer to the following section of the documentation:
>
>
> http://search.cpan.org/~tmtm/Class-DBI/lib/Class/DBI.pm#DEFINING_SQL_STATEMENTS
>
> Do you intend for the first '?' and second '?' to be the same value? I'll
> assume yes. I would start with adding the following *untested* code to
> your
> "link" package:
>
> # You might want to add a "GROUP BY __ESSENTIAL__" clause to the following
> # SQL if contact_id_1 can equal contact_id_2 or else the same entry could
> # show up twice....
> __PACKAGE__->set_sql(by_contacts => <<'');
> SELECT __ESSENTIAL__
> FROM   __TABLE__
> WHERE  contact_id_2 = ?
> UNION
> SELECT __ESSENTIAL__
> FROM   __TABLE__
> WHERE  contact_id_1 = ?
>
> Then, in your "contact" package, you could add the following:
>
> sub links {
>     my $self = shift;
>     return Link->search_by_contacts($self->contact_id,$self->contact_id);
> }
>
> Make sense?
>
> Hope this helps,
> Ed
>

_______________________________________________
ClassDBI mailing list
ClassDBI-Ra3b/[email protected]
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi