Cakephp complex find "NOT IN"

Question

I have two tables called calendar_colour and user,

calendar_colour
(
   colour_id int primary key,
   colour varchar(15)
)


user
(
   id int primary key,
   name varchar(30),
   color int,
   foreign key(color) references calendar_colour(colour_id)
)

In the add function of the user I have to choose a colour from a dropdown box. But I want to populate the dropdown using the colours that are not already taken by previous users. I tried using a find command but it seems to be wrong.

$curColours = $this->EventType->query('select color from event_types');

$this->set('colours', $this->EventType->CalendarColour->find('list',array('conditions'=>array('NOT',array('CalendarColour.colour_id' => $curColours)))));

I use the array $colours to populate the dropdown box. What is the correct way to write the find command that finds the colours that are not used by any users.

Thanks.

Solution

You almost had it:

$this->EventType->CalendarColour->find('list', array(
  'conditions' => array(
    'NOT' => array( // There's your problem! :)
      'CalendarColour.colour_id' => $curColours
    )
  )
));

Comments

[user1563210]

Thanks that worked to an extent! But I got an error saying unknown column "Array" when I added a user and then tried to add another one.