Gossamer Forum
Home : Gossamer Threads Inc. : Discussion :

Question about GT::SQL::Table

Quote Reply
Question about GT::SQL::Table
Hi,

The following lines in the documentation of GT::SQL::Table->select() is not true for related tables.
Code:
Nothing in the select will be quoted, so to use functions, simply pass in what you want:
my $sth = $obj->select ( [ 'COUNT(*)' ] );
which turns into ``SELECT COUNT(*) FROM Table''.

Can that be fixed?

Thanks, Jasper

http://www.bookings.org
Quote Reply
Re: [jaspercram] Question about GT::SQL::Table In reply to
Quote:
The following lines in the documentation of GT::SQL::Table->select() is not true for related tables.

The documentation for GT::SQL::Table and GT::SQL::Relation is on seperate pages.

Last edited by:

Paul: Dec 18, 2002, 7:28 AM
Quote Reply
Re: [Paul] Question about GT::SQL::Table In reply to
I was talking about GT::SQL::Table->select(), not about GT::SQL::Relation->select(). The fact that Table->select() calls Relation->select() doesn't change anything to the API of Table->select() .

Jasper

http://www.bookings.org
Quote Reply
Re: [jaspercram] Question about GT::SQL::Table In reply to
I'm not sure why you think it is wrong?

It looks ok to me unless I'm missing something?
Quote Reply
Re: [Paul] Question about GT::SQL::Table In reply to
This does not work:
Code:
my $table = $home->{sql}->table('transactions','forms');
$table->select_options("GROUP BY year, month, transactions.affiliate_id",
"ORDER BY year ASC, month ASC, transactions.affiliate_id ASC");
my $sth = $table->select({"transactions.creditslip_id" =>$slip_id },
[ "transactions.affiliate_id",
'EXTRACT( YEAR FROM forms.enddate ) as year',
'EXTRACT( MONTH FROM forms.enddate ) as month',
'SUM(transactions.aff_euro_fee) as sum']);

But this does work:
Code:
my $table = $home->{sql}->table('transactions','forms');
$table->select_options("GROUP BY year, month, transactions.affiliate_id",
"ORDER BY year ASC, month ASC, transactions.affiliate_id ASC");
my $sth = $table->select({"transactions.creditslip_id" =>$slip_id },
[ "transactions.affiliate_id",
\'EXTRACT( YEAR FROM forms.enddate ) as year',
\'EXTRACT( MONTH FROM forms.enddate ) as month',
\'SUM(transactions.aff_euro_fee) as sum']);

Sorry for being so vague....

Jasper

http://www.bookings.org
Quote Reply
Re: [jaspercram] Question about GT::SQL::Table In reply to
Yep because SUM and EXTRACT are functions which appear in the WHERE clause and so should not be quoted so you have to reference them. COUNT however comes after SELECT in the query which GT::SQL handles which is why you don't have to pass in a scalar ref.

Last edited by:

Paul: Dec 18, 2002, 8:01 AM
Quote Reply
Re: [Paul] Question about GT::SQL::Table In reply to
The manual also says the following:
Quote:
So you can pass in a hash reference which represents the where clause, and an array reference where represents what you want to select on.
Combined with the part in the manual that I quoted in my first post, I concluded that entries in the array reference do no have to be referenced.

http://www.bookings.org
Quote Reply
Re: [jaspercram] Question about GT::SQL::Table In reply to
That's correct. By default anything after SELECT is not quoted, ie...

SELECT col1, col2 FROM TABLE

...you wouldn't do:

SELECT 'col1', 'col2' FROM TABLE

A COUNT(*) goes into that part....

SELECT COUNT(*) FROM TABLE

....and so GT::SQL knows not to quote it which is why you don't have to reference it, nor do you have to reference any column you pass in as an arrayref.

However apart from that case, in order for GT::SQL to know that you are passing in a function and not a column then you have to reference it, which tells GT::SQL not to quote it.

Last edited by:

Paul: Dec 18, 2002, 8:15 AM
Quote Reply
Re: [jaspercram] Question about GT::SQL::Table In reply to
Hi,

The issue is because GT::SQL::Relation has to look through what you are selecting to handle things like 'table1.col1', 'table2.col1', etc. So it needs to know what is a function, and what is a column name. I think it could you some improving though, so we'll take a look at it, but for now, yes, functions need to be escaped in the select for relation, but not for table.

Cheers,

Alex
--
Gossamer Threads Inc.