I have a mojolicious app where I am using lots of queries, which are hard coded, so I am trying to differentiate between code and query by having new .sql files for each query.
I have some problems with the following query:
my $sql = $self->db->query(qq(
select
a,
b,
c
from
table
where
date_format(d, "%m") >= $month
and
date_format(d, "%m") <= $month
and
date_format(d, "%Y") >= $year
and
date_format(d, "%Y") <= $year
and
if(? = "", a, a=?)
), $a, $a);
The query above work perfect, but when I try to read it from a file my result is null.
my $path = Mojo::File->new('/path/to/file')->slurp;
my $sql = $self->db->query($path, $month,$month, $year, $year, $a, $a);
It doesn't matter if I use double quotes, single quotes or each perl function of quoting, the result is always null. When I get my params with $sth->{ParamValues} I get the right params for each call.
After coupule of hours of debugging and testing, I found out that the problem lies on the sql function "date_format" which is not called properly, but I couldn't find out why not. An alternative could be to format the time inside perl, but is not a beautiful way to handle that. I also tried to use sql variables for setting the params.
My sql file looks like:
select
a,
b,
c
from
table
where
date_format(d, "%m") >= ?
and
date_format(d, "%m") <= ?
and
date_format(d, "%Y") >= ?
and
date_format(d, "%Y") <= ?
and
if(? = "", a, a=?)
The solution was to use the interval function of mysql which is smart enough to calculate the date.
For the parameters I used the sprintf function and used one variable instead of 2.
my $date = sprintf('%4d-%02d-01');
select sum(a), sum(b) from t1 where c between $date and $date + interval 1 month;