Cakephp搜索不存在的表条目的条件

Question

I'm using CakePHP 2.3.8 and trying to find Users who have not configured options. If there is no entry found for User 1 in the options table, then that user should be selected

The tables

users
id    |    username   |      password     |category_id    |     registration_date    


options
id    |    user_id    |    display_email   |    allow_contact

A user does not have to be entered in the options table. What I'm trying to do is find all of the users that do not have an entry in the options table.

$this->paginate = array(
                        'conditions' => array(
                            'User.category_id' => $category_id,
                            //condition if the options table entry for the user is not set
                        ),
                        'joins' => array(
                            array(
                                 'table' => 'options',
                                 'alias' => 'Option',
                                 'type' => 'inner',
                                 'conditions' => array(
                                      'Option.user_id' => 'User.id'
                                 )
                            )
                        )
                     )

Answer 1

First change your join type to left. This will get you all users, even those that have no options. An inner join will only get those users that have an option -- almost the opposite of what you want.

Then add a condition to look for rows where Option.id is null. Users that do not have an option associated with them will have null for all the columns that came from Option.

$this->paginate = array(
       'conditions' => array(
              'User.category_id' => $category_id,
              'Option.id' => null
        ),
        'joins' => array(
               array(
                     'table' => 'options',
                     'alias' => 'Option',
                     'type' => 'left',
                     'conditions' => array(
                           'Option.user_id' => 'User.id'
                      )
               )
        )
  );