CakePHP / MySQL：查找模型本身和指定模型的条件

Question

Consider, we have users and the users have accounts (one-to-many). Now I am completely stuck at the following problem: we provide a $query and want to return all users:

• whose fullname LIKE %$query% OR
• whose email LIKE %$query% OR
• who have accounts, of which organisation LIKE %$query%

I already tried joins and containable behavior but no success. With joins we could achieve this, but the problem is, the data is not structured in the cake way (we get only one row pro match even if same user occurs multiple times). Resorting the data will not be OK because this controller must be very fast.

Help!

Answer 1

Try with -

$this->User->find(
    'all',
    array(
     'conditions' => array(
                      'OR' => array(
                               'User.fullname LIKE' => '%'.$query.'%',
                               'User.email LIKE' => '%'.$query.'%',
                               'Account.organisation LIKE' => '%'.$query.'%',
                              ),
                     ),
     'contain'    => 'Account',
    )
);

Using alias -

$qry = "select * from users as u, accounts as a where u.fullname like %$query% or u.emaillike %$query% or a.organisation like %$query%";
$results = $this->query($qry);