Search code examples
phpsqlpropel

Propel translating the wrong table names


            ->joinOrigin('origin')
            ->joinLocation('location', \Criteria::LEFT_JOIN)
            ->where("LOWER(IF(location.code IS NOT NULL,location.code, origin.code)) NOT IN ?", $not_in_old_job_search_array)

Is a part of a Propel query I am porting from raw SQL. location and origin are two foreign keys (declared as such in the schema.xml) which point to the same table. Propel adds the joins properly:

INNER JOIN `idb_countries` `origin` 
ON (idb_ads.idb_countries_ididb_origin=origin.ididb_countries) 
LEFT JOIN `idb_countries` `location` 
ON (idb_ads.idb_countries_ididb_job_location=location.ididb_countries)

but the where clause comes out like this:

LOWER(IF(idb_countries.code IS NOT NULL,idb_countries.code, idb_countries.code)) NOT IN (:p5,:p6,:p7)) 

How can I avoid this?

EDIT: My propel version is 1.7.0, and my php version is 5.4.9


Solution

  • I think yours is a simpler problem than that described at Propel: selecting columns from aliased join tables.

    Try using this where() call:

    ->where(
        "LOWER(IF(location.Code IS NOT NULL,location.Code, origin.Code)) NOT IN ?",
        $not_in_old_job_search_array
    )
    

    You'll notice the only difference is that I've changed "code" to "Code". The where() method is expecting the PHP name of the column, not the actual column name. The aliases are stored with the PHP name, so "Code" lets it know to use the alias while "code" does not.