[Dbix-class] SELECT from multiple tables

[Matt S Trout]

On Fri, Apr 18, 2008 at 11:57:30AM +0200, Emmanuel Quevillon wrote:
> Hi,
> 
> I am a big fan of DBIx::Class, I think that is a great tool.
> However, I did not find an easy way to query database using 
> multiple table in the FROM clause with DBIx::Class e.g:
> 
> SELECT p.id, p.name, q.id, q.level FROM public p, group g 
> WHERE p.id=g.id AND p.name LIKE '%class%';
> 
> I know you'll answer, "Why don't you use join?". Well when I 
> use join, my queries are very slow sometimes, and I realized 
> that querying this way is much faster (in my case at least).

Then your database is broken.

I'm going to go out on a limb and guess it's Oracle; it'd help if you'd
bothered giving us more information but I'll guess with what little you've
supplied.

Assuming it -is- Oracle, you want to force usage of the Oracle::WhereJoins
module -

__PACKAGE__->storage_type('::DBI::Oracle::WhereJoins');

which is designed for old Oracles that don't support ANSI JOIN syntax, but
can sometimes be useful with ones that do but where the optimiser picks
better indexes if you put the join conditions in the WHERE clause [0]

[0] No, I don't know why they did that. Even MySQL's excuse for an
optimiser is -reliably- retarded.
-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/