Make a filter query search multiple columns | Symfony 1.4

Question

I am working on an Symfony 1.4 project and need to customize a backend filter "name", so that the column "name_full" is searched simultaneously with the "name" column.

I thought of doing it like that in my FormFilterClass:

public function addNameColumnQuery(Doctrine_Query $query, $field, $value) {

    $rootAlias = $query->getRootAlias();
    return $query->where(''.$rootAlias.'.name LIKE ?', "%$value%")
                 ->orWhere(''.$rootAlias.'.name_full LIKE ?', "%$value%");
}

but that doesn't work as it gives me a wrong result set. And I fail to find the file where I could dump the complete dql for debugging.

Please point out where I could dump the complete dql or even tell me what is wrong with my approach.

Thank you!

EDIT: This is the correct function as suggested by Michal and tweaked by me:

public function addNameColumnQuery(Doctrine_Query $query, $field, $value)
{
    $rootAlias = $query->getRootAlias();
    return  $query->addWhere(
        ''.$rootAlias.'.name LIKE ? OR '.$rootAlias.'.name_full LIKE ?',
        array($value, $value)
    );
}

I found out that I will have to overload doBuildQuery() in my FilterFormClass. How exactly do to what I want? I am still hunting for the solution. — Florian Lucke, Dec 01 '13 at 20:16
You can check the query from the development toolbar when you use the dev environment. — Michal Trojanowski, Dec 02 '13 at 08:48

score 2 · Accepted Answer · answered Dec 02 '13 at 08:53

Try changing this to:

$query->addWhere(
    '$rootAlias.'.name LIKE ? OR '.$rootAlias.'.name_full LIKE ?',
    array("%$value%", "%$value%")
);

By using ->where() you overwrite the whole where part of the query so you could be deleting some conditions that were applied before the function addNameColumnQuery was called. Also by using the above statement you will make sure that the two conditions joined with OR will be in parenthesis, so you will have

condition1 AND condition2 AND (name LIKE '' OR name LIKE '')

not

condition1 AND condition2 AND name LIKE '' OR name LIKE ''

Thanks a lot Michal! I had to slightly tweak your function (see edit above) but now it works a treat! — Florian Lucke, Dec 02 '13 at 11:32

Make a filter query search multiple columns | Symfony 1.4

1 Answers1