Howdy,

I'm just getting to grips with GT::SQL but was hoping for a little help with something....

I'm currently using a loop for something that I think I could get rid of with the following:

$db = $DB->table('Links');
$db->select_options("ORDER BY $order", "LIMIT $foo,$bar");
%links = $db->select( { ID => \@ids } )->fetchall_list;

However %links seems to be empty, even though looping through it like:

for (@ids) {
$link = $db->select( { ID => $_ } )->fetchrow_hashref;
print %$link;
}

...seems to work.

GT::Dumper just prints undef.

Any ideas?

Whilst I'm rambling, I believe you can do:

$db->select( { ID => 1 } )->fetchrow_hashref;

to select all cols right?

This also seems to work;

$db->select('*', { ID => 1 } )->fetchrow_hashref;

Is either way ok? (obviously the first way is better)

If you don't want WHERE can you just do:

$db->select->fetchall_list;

??

What does $db->select; return?


Thaaaanks. Sorry for rambling :)

OOpps....btw....how is fetchall_list returned? Is that a hash of hashes or something?

Hi,

%links = $db->select( { ID => \@ids } )->fetchall_list;

There are a couple things wrong with that.

First, you can't do ID => \@ids in 2.0.5, we only recently added this feature into GT::SQL (probably saw this in Gossamer Forum?). What it does is when you say ID => \@ids, it turns that into ID IN (1,2,3,4,5) for the sql server.

So in 2.0.5, you need to pass that in manually like:

my $str = '(' . join (",", @ids) . ')';
my $cond = GT::SQL::Condition->new ('ID', 'IN', \$str) ;
my $sth = $db->select ( $cond );

What that will do is turn your select into SELECT * FROM Links WHERE ID IN (1,2,3,4) to get all the links you want.

Second, fetchall_list returns everything as one large list, so if you did that on a table with 3 columns a, b, and c you would get returned:

(a_val1, b_val1, c_val1, a_val2, b_val2, c_val2, ... );

as one big list! What you probably want is ->fetchall_hashref, which returns an array reference of hash references.

Where fetchall_list is very useful is when you are returning a single element, or two elements. For instance, if you need all the link id's that match a ceartain query, you can do:

my @ids = $db->select ( ['ID'], { Contact_Name => 'Alex' })->fetchall_list;

I now have all the matching ids in @ids (It works because I only asked to return ID, not the entire record).

It's also useful when you have 2 columns. For instance, say I want a hash of category id to category names of root level categories, I can do:

my %cats = $db->select ( ['ID', 'Full_Name'], { FatherID => 0 })->fetchall_list;

%cats is now a hash of category id => category full name.

Third, as for what to select, if you want everything, just don't specify.

Hope that helps,

Alex
--
Gossamer Threads Inc.

Thanks for the detailed reply. I'm using 2.0.1 and not 2.0.5 however.

Does the same still apply or is the updated SQL module distributed with 2.0.1?

Hi,

Do you mean 2.1.0? Yes, the beta will spport it, but anything less won't.

Cheers,

Alex
--
Gossamer Threads Inc.

Yes sorry. 2.1.0

It isn't working with 2.1.0 so it must be my code then although I thought I had it right :(