0

I'm trying to filter results of a table based on two associated tables using a logical disjunction (OR).

For example I want to find all Products filtering on Reviews and Reviews.Users.

where Reviews.Users.super is ->eq(true)

OR

where Reviews.visibility is ->eq('visibility', 'public') AND Reviews.published is ->isNotNull('published')

using two ->innerJoinWith creates a conjunction.

this->Products->find()
  ->distinct()
  ->where(['Products.status' => 'current'])
  ->contain(['Reviews.Users'])
  ->innerJoinWith('Reviews', function($q) {
      return $q
        ->where(['Reviews.visibility' => 'public'])
        ->andWhere(['Reviews.published IS NOT' => null]);
    })
  ->innerJoinWith('Reviews.Users', function($q) {
      return $q
        ->where(['Users.super' => true]);
})
->all();

when trying to use leftJoins as suggested by @ndm I get a 

$this->Products->find()
  ->leftJoinWith('Reviews')
  ->leftJoinWith('Reviews.Users')
  ->where([
    'OR' => [
       'Reviews.Users.super' => true,
       'Reviews.visibility' => 'public'
     ]
  ])
  ->andWhere([
     'Reviews.published IS NOT' => null
   ])

Column not found: 1054 Unknown column 'Reviews.Users.super' in 'where clause'

waspinator
  • 6,464
  • 11
  • 52
  • 78
  • 1
    You need to use `LEFT` joins and put the conditions in the querie's `WHERE` clause... **https://stackoverflow.com/questions/45868157/cakephp-3-3-15-multiple-matching-chained-with-or/45868805#45868805** – ndm Jul 10 '19 at 17:50
  • I'm having trouble with narrowing down the query with a `where` clause when `leftJoinWith` is deeply nested. Unknown column `Reviews.Users.super` for example. – waspinator Jul 25 '19 at 00:52
  • 1
    The columns of joined associations become part of the main query, there's no need for nesting when referencing them, ie use `Users.super`. – ndm Jul 25 '19 at 08:47

0 Answers0