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.

Backhouse

Might I suggest you use multiline to write any type of query? Like I did in my answer. It just makes it easier to understand :) – Antimatter

Answer 1

You almost had it:

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

Antimatter

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. – Backhouse