CODEWITHSUNDEEP
phpactiverecordormpropel
Roman
Roman

Reputation: 1277

Use only one join condition in Propel

I'm using Propel 2. I have a problem with joining conditions. Because of it I can't fetch the data using Propel API and I'm forced to use plain SQL with PDO. Here is my table structure:

<table name="categories" phpName="Categories">
  <column name="id"           type="integer" required="true" primaryKey="true" autoIncrement="true"/>
  <column name="parent_id"    type="integer" required="true" defaultValue="0"/>
  <column name="root_id"      type="integer" required="true" defaultValue="0"/>
  <column name="redirect_id"  type="integer" required="false" defaultValue="null"/>
  <column name="name"         type="varchar" size="255" required="true" defaultValue=""/>
  <column name="status"       type="char" sqlType="enum('active','deleted','disabled')" required="true" defaultValue="active"/>
  <foreign-key foreignTable="categories" phpName="Categories">
    <reference local="parent_id" foreign="id"/>
    <reference local="redirect_id" foreign="id"/>
  </foreign-key>
  <vendor type="mysql">
    <parameter name="Engine" value="InnoDB"/>
    <parameter name="Charset" value="utf8"/>
  </vendor>
</table>

I want to use the following SQL:

SELECT    c.id, c.name
FROM      categories c
LEFT JOIN categories p ON c.parent_id = p.id
WHERE     c.status = 'active' AND p.status = 'active' AND c.name LIKE '%Laptop%'

The Propel generated model's methods I use:

$categories = CategoriesQuery::create('c')
        ->filterByName("%{$name}%")
        ->filterByStatus('active')
        ->useCategoriesQuery('p')
            ->filterByStatus('active')
        ->endUse()
        ->find();

This way generated SQL always has two join conditions LEFT JOIN categories p ON (categories.parent_id=p.id AND categories.redirect_id=p.id). I've tried using join(), addJoin() with where(), etc. Nothing seems to help. Obviously the problem is in my knowledge of Propel.

Upvotes: 1

Views: 1860

Answers (1)

Ben
Ben

Reputation: 713

In general, I have found the most success doing complex self joins by combining the following methods:

Join::addExplicitCondition Join::setJoinType ModelCriteria::addJoinObject ModelCriteria::where

However, in this case, your issue is quite simple. You are asking Propel to use the composite foreign key, so it uses both columns in the composite foreign key for the query.

I would bet that your problem is that you are trying to use one foreign key, where you should be using two, separate foreign keys for two separate relationships.

For example, this might server you better:

<database name="default">
  <table name="category" phpName="Category">
    <column name="id"           type="integer" required="true" primaryKey="true" autoIncrement="true"/>
    <column name="parent_id"    type="integer" required="true" defaultValue="0"/>
    <column name="root_id"      type="integer" required="true" defaultValue="0"/>
    <column name="redirect_id"  type="integer" required="false" defaultValue="null"/>
    <column name="name"         type="varchar" size="255" required="true" defaultValue=""/>
    <column name="status"       type="char" sqlType="enum('active','deleted','disabled')" required="true" defaultValue="active"/>
    <foreign-key foreignTable="category" phpName="ParentCategory">
      <reference local="parent_id" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="category" phpName="RedirectToCategory">
      <reference local="redirect_id" foreign="id"/>
    </foreign-key>
    <vendor type="mysql">
      <parameter name="Engine" value="InnoDB"/>
      <parameter name="Charset" value="utf8"/>
    </vendor>
  </table>
</database>

with the query

$categories = CategoryQuery::create('c')
    ->filterByName("%{$name}%")
    ->filterByStatus('active')
    ->useParentCategoryQuery('p')
        ->filterByStatus('active')
    ->endUse()
    ->find();

which, in the Propel sandbox gives

SELECT category.ID, category.PARENT_ID, category.ROOT_ID, category.REDIRECT_ID, category.NAME, category.STATUS
  FROM `category`
  INNER JOIN `category` `p` ON (category.PARENT_ID=p.ID)
  WHERE category.NAME LIKE '%myname%'
  AND category.STATUS='active'
  AND p.STATUS='active'

Upvotes: 1

Related Questions