I am reading the GT docs and I came across:

my $count = $obj->count ($condition);


But what format should $condition be in?

For example, lets say I want to count all the links where hits > 0. What should $condition be set at? It seems to want something other than 'Hits' > 0.

Also This:

Find all rows with field_name > value:

field_name-gt => value



I can't seem to get this to work in a select either. Would someone mind showing me an example of how to use this?

(I am having those font size issues again... I can have 8 or 12 but not 10 point!!)


http://www.iuni.com/...tware/web/index.html
Links Plugins

$condition is a GT::SQL::Condition or a hashref.

eg...

$DB->table('Table')->count( { Foo => 'Bar' } );

SELECT COUNT(*) FROM Table WHERE Foo = 'Bar'

-OR-

require GT::SQL::Condition;
my $condition = GT::SQL::Condition->new( 'Hits', '>', 0 );

$DB->table('Table')->count( $condition );

SELECT COUNT(*) FROM Table WHERE Hits > 0

Also, have a look at the Condition help page for examples on how to format more complex conditions.

Cheers,

Alex
--
Gossamer Threads Inc.

Hi, thanks both (Alex, Paul).

I have read through the condition page, and I have actually used it sucessfully.

I was just curious to see if I could condense some of my function into the one liners you can with a simple where, but it seems is soon as there is something other than 'equal to' then you must use the condition statement.

Like my example for getting a count of all the links where hits > 0, is only 1 line if it is hits = 0.

I understand what I must do now, though.

Thanks again.


http://www.iuni.com/...tware/web/index.html
Links Plugins

In the SQL module that I wrote I made it so that I could do:

{ Col => ['>', 0] }

...I know that isn't very helpful to you but I wonder whether it could be added into the GT libs?...or maybe an arrayref is already being used for something else?

Paul... very good!

It would greatly simplify and reduce the number of line of code , hence reduce the chances of error, and clean up appearance.

I would love to see this including in the GT Library.

Good work on your part BTW!


http://www.iuni.com/...tware/web/index.html
Links Plugins

Nope, array refs are already used:

$table->select( ID => [1,2,3] );

translates to WHERE ID IN (1,2,3), which is very handy. =)

You can always do:

$table->select( GT::SQL::Condition->new('ID', '>', '5') );

as a one liner.

Cheers,

Alex
--
Gossamer Threads Inc.

Alex, now why couldn't I see that

Thank you very much! This will tidy up my code a lot!


http://www.iuni.com/...tware/web/index.html
Links Plugins

Ah yeah I forgot about IN's

For IN's with mine I do:

{ Col => ['IN', \@values] }

GT::SQL::Condition is really handy for complex things like:

NOT(A = 1) OR (B = 2) AND (C = 3)

which would look like:

my $cond1 = GT::SQL::Condition->new('A', '=', '1')->not,
my $cond2 = GT::SQL::Condition->new(
'B', '=', '2',
'C', '=', '3'
);
my $cond3 = GT::SQL::Condition->new($cond1, $cond2, 'OR');

The ability to nest things is what makes it really powerful.

Cheers,

Alex
--
Gossamer Threads Inc.

Oh yeah I wasn't suggesting to remove conditions...they are great...but for smaller conditions it would be great to be able to just do something like ['>', 0] or ['>=', 0] just for speed really instead of creating a condition.

Anyway it was just a passing thought.

I like this!


http://www.iuni.com/...tware/web/index.html
Links Plugins

The last argument to GT::SQL::Condition can be 'AND', or 'OR', and it will set the boolean that multiple things are joined by:

GT::SQL::Condition->new(
'a', '>', 5,
'b', '<', 50,
'OR'
);

It defaults to AND.

Cheers,

Alex
--
Gossamer Threads Inc.

Yes, I saw the 'AND' default in the docs... makes sense.

I think as a habit I am going to use these condition statements a lot more, even for '=' statements. It allows me to see some uniformity throughout my files when making calls to the tables.

Between table joins and this, I am going to be able to do some nifty things in my functions!


http://www.iuni.com/...tware/web/index.html
Links Plugins

Ok Alex, how about this one:

$lt->select( GT::SQL::Condition->new('Title', ' < ', ' ?') )->rows;

I want to select the number of rows where the 'Title' has a string length less than 5.

Maybe this one has to be done with the full select options / query....


http://www.iuni.com/...tware/web/index.html
Links Plugins

$t->select( GT::SQL::Condition->new('LENGTH(Title)', '<', 4) );

You are using a mysql function length(). See:

http://www.mysql.com/...tring_functions.html

Note, that this may not be portable now across MySQL, MS SQL, Postgres or Oracle. I think LENGTH() may be fairly safe, but am not 100% sure.

Cheers,

Alex
--
Gossamer Threads Inc.

Thanks again Alex.

Interesting point on the portability.

I will have to check this out. Perhaps the safer approach would be to use the perl length and make the function a couple of lines... but I will look all the same (I like these 1 liners!)


http://www.iuni.com/...tware/web/index.html
Links Plugins

Hi Alex, its Ian back at the SQL table!

Oppps, never mind.... I think I worked it out.


http://www.iuni.com/...tware/web/index.html
Links Plugins