[Dbix-class] trouble with using 'select' in a search clause

[Matt S Trout]

John Napiorkowski wrote:
> To be honest I am sure part of my problem is ignorance
> about the best way to use DBIx.  So your suggestions
> of any type are useful.  I've been using SQL for quite
> a long time and writing a statement like the one I
> show below is a very normal way for me to think.  My
> instinct is to try and get all the data I want in a
> single statement, since my training in SQL pushed me
> to consider performance issue.

It's ok, that sort of query is a normal way for me to think too - the only 
difference is that I'm able to mentally map DBIC queries to SQL and back 
because I've got a pretty good mental model of how they correspond (shockingly 
enough :). One of the major design goals of DBIx::Class is to be able to get 
the data you want in a single SELECT more often than not, since in 
database-backed applications the database is usually the hardest thing to 
scale - which usually means you don't need to start hand-setting things via 
select and as to achieve that aim. See below for a worked example for your 
current case.

> Basically I have a Members table that contains all the
> information about members for my site.  My requirement
> is to create a way for members to have an addressbook
> of email addresses and additionally to store
> information about those email addresses on a per
> member basis.  I could just create a one to many table
> "members_invitees" with a FK relationship to members,
> but I consider that bad form since it's possible that
> two members could have the same friend in their
> addressbook.  So that's why I have a many to many
> relationship here.

That's perfectly sensible. Assuming you have (pseudocoding a bit)

Member has_many member_invitees => MemberInvitee

MemberInvitee belongs_to invitee => Invitee

then

my @members = $schema->resultset('Member')->search(
                 { ... <condition> ... },
                 { join => { member_invitees => 'invitee' },
                   prefetch => { member_invitees => 'invitee' },
                 });

will get you back member objects where

foreach my $member (@members) {
   my @invitees = map { $_->invitee } $member->member_invitees;
}

won't result in another trip to the database at all, since the prefetch attr 
in the search will cause DBIx::Class to do (effectively)

SELECT members.*, members_invitees.*, invitees.*
FROM members LEFT JOIN members_invitees ON ... JOIN invitees ON ...
WHERE <condition>

in the first place, and all the related objects will be pre-populated as 
they're inflated from the results of the SELECT.

> I think that the solution you show can help me.  The
> reason I was looking at the select option is that this
> way maps back to the way I would think to do it in
> SQL, but I realize some things about my thinking have
> to change to use DBIx most correctly.  Right now I am
> only using this for a few weeks.  I'd like to create a
> little chart sometime of common SQL constructs and how
> to replicate them in DBIx; I think such a thing would
> really help newcomers like myself with a big SQL
> background but little or no experience using ORMs. 

That would be incredibly handy, yes. We've got a few such examples in the 
cookbook but they're all how a given DBIC query maps to SQL - a document 
showing a bunch of examples in SQL and how to map them back to DBIC would be 
an excellent addition to the manual and much appreciated.

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