CODEWITHSUNDEEP
joinpropel
user968892
user968892

Reputation:

Propel 1.5 How to make join with OR condition

I need to generate such SQL using Propel build criteria:

"SELECT * 
FROM  `table1` 
LEFT JOIN table2 ON ( table1.OBJECT_ID = table2.ID ) 
LEFT JOIN table3 ON ( table1.OBJECT_ID = table3.ID ) 
LEFT JOIN table4 ON ( table4.USER_ID = table2.ID
OR table4.USER_ID = table3.AUTHOR_ID )"

Is it possible to make join with or condition? Or maybe some other ways? Propel 1.5

Upvotes: 1

Views: 3705

Answers (2)

halfer
halfer

Reputation: 20467

The first two joins (table2, table3) are easy, if I recall correctly. Just make table1.OBJECT_ID non-required in your schema, and the left join will be used automatically.

Not immediately sure about the OR join. If you get stuck, one way to do it is to use the above in a raw query, and then "hydrate" objects from the resultset. Another way (very good for complex queries that are a pain to express in an ORM) is to create a database view for the above, and then add a new table in your schema for the view. It's cheating a bit for sure, but for some really complex master-detail things I did in a large symfony project, it was great - and it made query debugging really easy as well.

Upvotes: 0

PM Arking
PM Arking

Reputation: 21

Table1Query::create()
  ->leftJoinTable2()
  ->leftJoinTable3()
  ->useTable2Query()
    ->leftJoinTable4()
  ->endUse()
  ->condition('cond1', Table4::USER_ID . ' = ' . Table2::ID)
  ->condition('cond2', Table4::USER_ID . ' = ' . Table3::AUTHOR_ID)
  ->combine(array('cond1', 'cond2'), Criteria::LOGICAL_OR, 'onClause')
  ->setJoinCondition('Table4', 'onClause')
  ->find();

useTable2Query() is necessary because your information seems to imply that Table4 is related to Table2 and not to Table1, and so joining Table4 directly to Table1 will result in a series of fatal Propel errors. The "use" functionality bridges that relationship.

Upvotes: 2

Related Questions