RFC2: Core::Handle - make simple_query a little less simple

[thefatphil]

The simplest of joins is to take two tables, and join on a single field from each table. If we're agreed that's simple, then it is worth supporting.

So, rather than: $urow = database->quick_select('users', { login => $uname }); $crow = database->quick_select('countries', { id => $urow->{'country'} });

One can now do: $row = database->quick_select([ 'users', 'countries' ], { login => $uname }, { join_keys => ['country','id'] });

(defaulting to inner join), or: $row = database->quick_select([ 'users', 'countries'], { login => $uname }, { join_type => 'left', join_keys => ['country','id'] });

Signed-off-by: Phil Carmody pc+github@asdf.org

[bigpresh]

My initial reaction is "don't want to make the simple query stuff any more complicated at all, and I still in hindsight probably should have just had it wrap SQL::Abstract or similar" -but, this looks pretty simple and inline with the initial idea of just "make simple queries simple to express, fall back to SQL for anything hairy rather than force you to work out what set of weird operators etc would be needed to do what you want".

So, I'll take a closer look soon when my brain is a bit more with it, but it looks quite sensible - thanks!

[thefatphil]

This is the version of the feature I'm running with at home, and am quite happy with it, I even have some small extensions. I've repeatedly said "I should just be writing the SQL!", but since I wrote this I still haven't written a single real query, which I think means it's doing its job.

[bigpresh]

Having had a little bit more of a think, it definitely needs some doco to explain how to use it etc, but it seems like a fairly sensible addition which could be useful.

[thefatphil]

I'll be creating a parallel branch to this, as whilst doing some other changes (quick_count), I've changed the join_keys semantics, to make them more consistent accross the wider changes. So this can be put on hold until both can be compared.

Sneak preview... my @cnt_users=database->quick_count( ['users','countries'], { type=>['virtual','human'] }, { group_by=>{countries=>'name'}, join_type=>'LEFT', join_keys=>{users=>'country', countries=>'id'} } ); now generates (modulo github mangling): SELECT countries.name,COUNT(*) as count FROM users LEFT JOIN countries ON users.country=countries.id WHERE type IN (?,?) GROUP BY countries.name with params 'virtual','human'