[Dbix-class] More join problems

[Matt S Trout]

On Mon, Jan 09, 2006 at 09:24:23PM +0100, Jon Molin wrote:
> On 1/8/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> >
> > 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/
> > });
> 
> 
> Wouldn't that first search give back a ResultSet (since it has_many), so I'd
> have to actually fetch each MemberBooks to be able to then do
> search_related, again fetching them and so on before I can fetch the end
> result? Which in turn would mean not even future caching-capable versions
> would save me from a gazillion of queries?
> I mean somthing like:
> foreach (MemberBooks->search({ relation => 'has' })) { foreach
> ($_->search_related('Book')){ ... and so on

Erm. I was serious about the above code. As of the current branch,
search_related works on resultsets now too ... 
 
> 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 :)
> 
> 
> Hmm...and I thought you where the author of CDBICompat and ResultSet ;)

CamelCase for class names. not for method names. "perldoc perlstyle".
 
> 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.
> 
> Right, but as stated above, I can't figure out a way to search
> related->related as possible in sql joins without actually fetching them
> (which makes sence to me as it's hard to find relating rows to something
> unknown). If it's not possible I guess I could go for just using the dbh but
> it'd feel like cheating .The excessive amounts of sql queries is something I
> can live with since it seems relations like:
> __has_many ('mb =>  'MemberBooks', { 'foreign.member_id' => {'!=', '
> self.member_id' }});
> are planed to be suported, and I doubt I'll finish this before that :)

When you call ->search_related on a resultset, you get back a resultset on
the foreign class with all the conditions still joined in. And it still
hasn't run any queries yet.

Check out a copy of the branch and try it :)

-- 
     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/ +