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

[John Napiorkowski]

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.

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.

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. 
-john



--- Toby Corkindale <tjc at wintrmute.net> wrote:

> On Wed, May 17, 2006 at 01:47:36AM -0700, John
> Napiorkowski wrote:
> > Hi,
> > 
> > I'm trying to create a query to perform something
> like
> > this:
> > 
> > SELECT me.*, invitees.* 
> >   FROM members_invitees me  
> >   JOIN invitees invitees 
> >   ON ( invitees.invitee_id = me.invitee_id ) 
> > WHERE ( member_id = 1 );
> 
> [snip]
> 
> > So the table "members_invitees" is a many to many
> > table between "members" and "invitees".
> > 
> > According to the documentation I can append
> columns to
> > the select statement.  Using the example given I
> > created a query (this is using Catalyst but should
> > show my meaning):
> > 
> > my $rs =
> $c->model("db::members_invitees")->search(
> > 		
> >   {
> >     member_id => $member->member_id
> >   },
> >   {
> >     select  =>['me.*','invitees.*'],
> >     join  => 'invitees',
> >   }
> > 
> > );
> 
> I'm just wondering, is there a good reason that you
> need to try and force those
> extra columns to be included?
> 
> Why not just access them via the relationship
> accessor after you've received
> the results of the search?
> 
> ie. 
> my $rs = $c->model('db::members_invitees')->search(
>     { member_id => $member->member_id }
> );
> while (my $i = $rs->next) {
>     print "This member is " . $i->invitees->foo;
> }
> 
> 
> Or perhaps, just do a search on Invitees directly,
> if that's what you're after?
> my $rs = $c->model('db::invitees')->search({
>         'member.id' => $member->id
>     },
>     {
>         join => { 'members_invitees' => 'members' }
>     }
> );
> 
> -toby
> 
> 
> -- 
> Turning and turning in the widening gyre/The falcon
> cannot hear the falconer;
> Things fall apart, the centre cannot hold/Mere
> anarchy is loosed upon the world
> (gpg --keyserver www.co.uk.pgp.net --recv-key
> B1CCF88E)
> 
> _______________________________________________
> List:
>
http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN:
>
http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com