[Dbix-class] More join problems

[Jon Molin]

Hi list,

I've got a new question about joins. I have three tables (*-marked being
primary key):
book (book_id*, title), member_books(book_id*, member_id*, relation),
member(member_id*)

I'm trying to get "what books does other members want that current user
have". In sql:
SELECT mb2.member_id, mb2.book_id, b.title
       FROM member_books mb1, member_books mb2, book b
         WHERE mb1.member_id=? AND mb1.member_id <> mb2.member_id AND
               mb1.relation='has' AND mb2.relation='wants' AND
mb1.book_id=mb2.book_id AND
               b.book_id order by b.title

Then I want to ask "what books does these users have" for each user:
SELECT mb.book_id, b.title FROM member_books mb, book b
  WHERE mb.member_id = ? AND mb.book_id=b.book_id AND mb.relation='has'


I've set up relations for Book->has_many(MemberBooks),
Member->has_many(MemberBooks), and MemberBooks having one of each.

I guess one way would be to Member->MemberBooks(relation =>
has)->Book->MemberBooks->(relation => wants)->Member and then
Member->MemberBooks(relation => has)->Book for each of thoose members. That
road feels like an awful lots of queries, and no simple way to sort the
books on titles rather than member/book_id.

Is there a smoother way to do this in DBIx or should I make a sub in Member
that uses $self->storage->dbh and in a (for me) not very DBIx-ish way
putting the results in some homebrewn struct?

Thanks in advance

/Jon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060108/02038034/attachment-0001.htm