This is a post in a series
about DBIx::Perlish Perl module.
Previous posts in the series are linked at the bottom.
Coming back to the original example,
my $uid = 42;
my @r = db_fetch {
my $u : users;
$u->groups_id == groups->id;
$u->id == $uid;
};
One more thing to note about this, before moving on:
when you specify more than one filtering expression,
there is an implicit and between them - so that
only those rows which satisfy all expressions
will match.
What more the language does to be useful?
Needless to say, all the normal binary operators are supported,
so your expectation that arithmetics work is not destroyed:
my $uid = 40;
my @r = db_fetch {
users->id == $uid + 2;
}
String concatenation also works, including interpolation:
my @r = db_fetch {
my $u : users;
return "$u->first_name $u->last_name";
}
This last example shows that DBIx::Perlish
interpolates columns represented as method calls
in a string. This would normally not work in a “normal”
Perl.
If you are paying attention,
you can see that there is another new thing in
the last example, namely, the presence of the return
statement.
If there is no return statement in the query sub,
all columns will be returned;
it is completely analogous to SQL’s SELECT * statement.
So you use the return statement when you want to control
what is returned by the query.
In this case it will be the concatenation of the column first_name,
a space character, and the column last_name from the table users.
How the query result is returned?
There are four possibilities;
which one is chosen depends on what the return statement,
if any, specifies, and what context (list or scalar) the db_fetch
is being used in.
If you call db_fetch in a scalar context,
you indicate that you are only interested in one row
of the resulting data set. Using list context you
indicate that you want all rows back.
So the context controls how many rows you want back.
On the other hand, if you specify a return statement
with precisely one value, you indicate that you are only
interested in one particular column of the resulting data
set. The absence of the return statement or a return
statement with a list of values indicates that you want
several columns back.
So the return statement controls how many columns within
a row you want back.
Combining two possibilities for rows with two possibilities
for columns, you get four possible combinations.
Let’s illustrate these possibilities with examples.
- Scalar context, single-value return:
my $name = db_fetch { return user->name };
print “The name of some user is $name\n”;
You get one row with one column back - a single
scalar value.
- List context, single-value return:
my @names = db_fetch { return user->name };
print “The names of all users are @names\n”;
You get an array with column values back.
- Scalar context, more than one column returned:
my $u = db_fetch { return user->id, user->name };
print “The name of a user with id $u->{id} is $u->{name}\n”;
Please note that you get a hash reference back with the keys
being the names of the columns returned.
- Finally, the most common case of the list context with
more than one column returned:
my @u = db_fetch { return user->id, user->name };
for my $u (@u) {
print “$u->{id}:\t$u->{name}\n”;
}
Observe that you get an array of hash references back.
Now, you might ask, what will be the names of the columns in a case
like this:
my @r = db_fetch {
my $u : users;
return $u->id, "$u->first_name $u->last_name";
}
One of the columns is obviously “id”, but what about the other one?
How do you refer to it in the result you’ve got?
The answer is - it is database-dependent and can be pretty much
anything. So you ask the next question: “can I control that?”.
Yes, you can, by prepending the offending nameless column
with the name you want for it in the return statement:
my @r = db_fetch {
my $u : users;
return $u->id, full_name => "$u->first_name $u->last_name";
}
More in the next post.
First post, Getting rid of SQL from Perl code
Second post, A walk through an example