[Dbix-class] Dbix-class Digest, Vol 15, Issue 3

[Nathan Lewis]

Hi Everyone,

Appologies in advance if this is a stupid and/or frequently asked
question but what is the correct syntax for Select of 'COUNT( * )' right
now?

In Version 0.07000

I have tried 

$rs->search(undef,
  	{ select => ['status', { count => 'me.tableid' }],
     group_by => [qw/date(timestamp) status/] });

As per the docs, or my understanding of them, but I just get

"DBIx::Class::ResultSet::search(): No such column HASH(0x22c5e80)

Then I tried taking out the hash ref around count and got the error

"DBIx::Class::ResultSet::search(): No such relationship 'count( me'


Thanks,

Nathan



Date: Sat, 02 Sep 2006 21:53:01 +0100
From: Matt S Trout <dbix-class at trout.me.uk>
Subject: [Dbix-class] Explicit ASTs (ping nate)
To: dbix-class at lists.rawmode.org
Message-ID: <44F9EF2D.7000802 at trout.me.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I've been doing some thinking about looking at refactoring SQL::Abstract
rather than trying to merge our existing horrible hacks upstream; Nate's
said to me he's never seen a patch he liked the feel of to add the
::Limit feature set so this is our chance to find one with him watching
and telling us when we get it wrong  :)

I think the basic concept we want is to have a two-stage generator - so
something like

   DWIM AST -> explicit AST -> SQL

The way Object::Relation does this stuff is quite nice, but it only
really handles WHERE condition type stuff, so we can take it as
inspiration but it's EINSUFFICIENT. So here's a rough cut at a "full"
AST (I'm going to present the AST and the SQL, the SQL::Abstract style
syntax is left as an exercise for the reader :)

I reckon the basic query structure can be a hashref since we have
@lots_of_clauses (and since we're bound to miss something first time
round) but each clause should probably be in arrayref form, so we have
something like

   foo = 'yay' AND bar LIKE 'wooho%'

becoming

   [ -op, 'AND',
     [ -op, '=', [ -name, 'foo' ], [ -bind, 'yay' ] ],
     [ -op, 'LIKE', [ -name, 'bar' ], [ -bind, 'wooho%' ] ],
   ],

and a function call like

   COUNT( * )

would become

   [ -func, 'COUNT', '*' ]

the basic principle here is that an op generally takes a specific number
of arguments, whereas a function is a containing clause that can take a
potentially arbitrary number. So a complete statement might be something
like

   SELECT my.a, my.b FROM my_table my WHERE spork = 1

and that would become

   {
     select => [ [ -name, 'my', 'a' ], [ -name, 'my', 'b' ] ],
     from => [ [ -alias, 'my_table', 'my' ] ],
     where => [ [ -op, '=', [ -name, 'spork' ], [ -bind, 1 ] ] ],
   }

which is ... spectacularly ugly, but I -think- explicit enough. Fun will
be handling specific things like

   MATCH (title,body) AGAINST ('database')

but I guess that's just

   [ -func, 'MATCH',
     [ [ -name, 'title' ], [ -name, 'body' ] ],
     [ [ -bind, 'database' ] ]
   ]

and have something like "func_specific_$func" and "func_generic" methods
on the emitter, so MATCH gets handled by func_specific_MATCH whereas
something "normal" like COUNT can go through func_generic (we'd
enumerate the specific methods at instantiation time for performance, I
think).

Thoughts?

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



http://www.bbc.co.uk/

This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system. 
Do not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received. 
Further communication will signify your consent to this.