Stuck on the Moose Case
Another Case Day here in the Moose-pen
So I decided to do some more case work today specificity their rather odd little SQL to start;
SELECT CASE WHEN Price < 100 THEN 'under 100$'
WHEN Price in (105, 110,120) THEN Price 105,110 or 120$'
ELSE 'Over 120$' END
FROM Products
and the above comes out as this test;
{
caption => 'Retrieve with case using In clause',
key => 'elements',
elements => [
{
whens => [
{
left => { name => 'Price', },
right => { value => '100' },
operator => '<',
statement => { value => 'under 100$' }
},
{
left => { name => 'Price' },
right => [
{ value => '105' },
{ value => '110' },
{ value => '120' },
],
operator => 'in',
statement => { value => 'Price 105,110 or 120$' }
},
{ statement => { value => 'Over 120$' } },
]
}
],
retrieve => {
sql =>
"SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ? ELSE ? END FROM Products",
params => [
100, 'under 100$', 105, 110,120, 'Price 105,110 or 120$','Over 120$'
]
},
}
and on my first run I get a fail;
Expected SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ? ELSE ? END FROM Products
Generated SELECT CASE WHEN Price < ? THEN ? WHEN Price in ?,?,? THEN ? ELSE ? END FROM Products
So two quick things I am missing my parentheses for my 'IN' an my 'IN' is in lower case. Now I have looked at the second problem before in my '32_where_operators.t' test case but the way I call that code the items has to be a 'Predicate' class in this case I am dealing with an “When' class.
Fortunately a 'when' is just an extended version of a 'predicate' class so I think I can get away with this little change to the '_field_sql' sub;
...
elsif (ref($element) eq "Database::Accessor::Case::When"){
++ return $self->_predicate_sql($element);
-- return join(" ",$self->_field_sql($element->left(),$use_view)
-- ,$element->operator
-- ,$self->_field_sql($element->right(),$use_view));
}
elsif (ref($element) eq "Database::Accessor::Expression"){
…
and this time I get;
Expected--> SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ?
ELSE ? END FROM Products
Genrated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price
IN (?,?,?) THEN ? ELSE ? END FROM Products
ok I got the correct parentheses in place and even my IN is in UC but I see that now I have the 'table/view' name appearing in my generated SQL. So I solved one problem but I see I will have to fix up that 'table/view' issue I believe my default on that is to always show the 'table/view' So I have some changes to do.
After changing my expected results for my first test I reran and got
Expected-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price >= ? AND Products.Price <= ? THEN ? WHEN Products.Price > ?
AND Products.Price <= ? THEN ? ELSE ? END price_group FROM Products
Gennerated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price >= ? AND AND Products.Price <= ? THEN ? WHEN Products.Price >
? AND AND Products.Price <= ? THEN ? ELSE ? END
I am getting an extra and in there I think I can get rid of that by taking out a little code in the '_field_sql' sub where I check for a condition on the when
...
foreach my $condition (@{$when}){
-- $condition_sql .= " ".$condition->condition." "
-- if ($condition->condition);
$condition_sql .= $self->_field_sql($condition,0);
...
and on this run I get;
ok 1 - Retrieve with case statement in elements retrieve SQL correct
ok 2 - Retrieve with case statement in elements retrieve params correct
ok 3 - Retrieve with case using In clause retrieve SQL correct
ok 4 - Retrieve with case using In clause retrieve params correct
so things are working out for me.
Leave a comment