Search code examples
phpmysqlpropel

Concat (2 fields) LIKE submitted value using Propel/Mysql/PHP

Propel/PHP/Mysql question for you. I have a search box that will search names in a table. I need to concat 2 fields, first_name and last_name, and than do a LIKE % submitted string %. All in propel.

This is what I currently have:

        $custQuery = CustomerQuery::create()
            ->withColumn("CONCAT(first_name, ' ', last_name)", "full_name")
            ->where("full_name LIKE %?%", $nameInput);

This gives the error:

Cannot determine the column to bind to the parameter in clause "full_name = ?".

Obviously I can't use the virtual column in the where statement. When I try to do the concat inside the where statement, I get the same error.

        $custQuery = CustomersQuery::create()
            ->where("CONCAT(first_name, ' ', last_name) LIKE %?%", $searchStr);

I'd prefer to avoid doing this without parameters:

        $custQuery = CustomersQuery::create()
            ->where("CONCAT(first_name, ' ', last_name) LIKE %$searchStr%");

It works, but I am looking for a more propel orientated method of doing this. Is there a way to do this without a where statement at all?

Thanks a ton!

Solution

  • You have to use pseudo column names. So, if your normal filter method for the customers.first_name column is ->filterByFirstName(), then when you use a ->where() or a ->condition() method, you also have to use the "FirstName" pseudo column name instead of 'first_name'.

    So, your problem query:

     $custQuery = CustomersQuery::create()
            ->where("CONCAT(first_name, ' ', last_name) LIKE %?%", $searchStr);

    would become:

    $custQuery = CustomersQuery::create()
            ->where("CONCAT(Customers.FirstName, ' ', Customers.LastName) LIKE ?", '%'.$searchStr.'%');

    I added the table name in there for you too for good measure, and you need to make the percent signs part of the paramater instead of part of the query statement.