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

[Toby Corkindale]

On Thu, May 18, 2006 at 01:11:46AM -0700, 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.

I've entered the DBIx::Class arena from the same background as you..
I've used to hand-crafting large SQL queries, with the emphasis being on
performance, data normalisation, and avoiding long locks due to transaction
serialisation.

I don't believe DBIx::Class will ever have the performance of pure hand-crafted
SQL, when used in complex situations. However, that's like saying that C++
won't have the performance of hand-written assembler - ie. It's true, but there
isn't a huge amount in it, and no-one is going to write large programs in pure
assembler, and you can always insert small pieces here and there if needs be
anyway.

DBIx::Class encourages you to think of your data as the conceptual related
objects they really are.

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

A many-to-many relationship sounds correct to me, too.

Then, you can retrieve a given member with:
my $member = $db->resultset('Members')->find(1);
# or use search, etc, to find them by something other than primary key.

# To list their addressbook:
my @addresses = $member->addresses; # assuming that's the many-to-many key
foreach my $entry (@addresses) {
    print "Name: " . $entry->name;
}

# An alternative way, using iterators (technically, the "correct" way)
my $addresses = $member->addresses;
while (my $entry = $addresses->next) {
    print "Name: " . $entry->name;
}

This assumes you have a correct many-to-many relationship setup in your DB
schema - yell if you need a hand, but the examples just about made sense once i
stared at them long enough in the manual.

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

Sounds like a useful device.
I don't recall seeing a chart like that before - but maybe some of the other
guys will chip in? I'm not an expert on DBIx::Class by any means :)

tjc

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