[Dbix-class] More join problems

[Matt S Trout]

On Sun, Jan 08, 2006 at 12:08:30AM +0100, Jon Molin wrote:
> 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.

On the current branch,

MemberBooks->search({ relation => 'has' })->search_related(
  'Book')->search_related(
    'MemberBooks', { relation => 'wants' }, { prefetch => qw/Member Book/ });

looks close to right. Not 100% sure though, your use of CamelCase for your
relationship names breaks my mental parser (words_with_underscores being the
perl-ish way :)

Also, please bear in mind that when you search_related on a resultset that
doesn't cause a query to be run; an rs never runs a query until you ask it
for its count, a pager or one of the records.

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +