CODEWITHSUNDEEP
phpsqlif-statementzend-framework2
ArVan
ArVan

Reputation: 4275

ZF2 table getaway select with IF() in columns

I want to get the following type of query with ZF2 using its tableGateway.

SELECT IF(column1 LIKE 'text', 1, 0)
FROM table
WHERE (column1 LIKE 'text' OR column2 LIKE 'text') AND status = 1

Is it possible to achieve this? I have tries the following:

$this->tableGateway->select(function (Select $select) use ($keyword) {

    $select->columns(array('id', "IF(column1 LIKE '{$keyword}', 1, 0)"), false)
           ->where(array(
                    new Predicate(
                        array(
                             new Like('column1', '%'.$keyword.'%'),
                             new Like('column2', '%'.$keyword.'%')

                         ),
                         Predicate::COMBINED_BY_OR
                    ),
                 'status' => 1
            ));
        });

But this gives the error saying Unknown column 'IF(column1 LIKE 'test', 1, 0)' in 'field list'. Is there a workaround?

EDIT

When I var_dump-ed the sql String, I got the following:

SELECT `id` AS `id`, `IF(table.column1 LIKE 'test', 1, 0)` AS `IF(table.column1 LIKE 'test', 1, 0)` 
FROM `table`
WHERE (`table`.`column1` LIKE '%test%' OR `table`.`column2` LIKE '%test%') 
      AND `table`.`status` = '1';

So it places my IF in ` quotes and treats it like column. So the question is how to disable that?

Upvotes: 1

Views: 1566

Answers (1)

ArVan
ArVan

Reputation: 4275

Thanks to this question I figured it out. Just need to put the IF statement in Expression, like this:

$select->columns(array('id',  new Expression("IF(column1 LIKE '{$keyword}', 1, 0)")), false)

Upvotes: 1

Related Questions