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

> 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