[Dbix-class] More join problems

[Jon Molin]

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



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 ;)


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 :)

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