CakePHP 1.3 query question

I am trying to convert working query to CakePHP find. So I fail, try to use Containable behavior and so on, but no luck. Please help me convert this SQL query to CakePHP find:

SELECT COUNT(DISTINCT(da.dealer_id))
    FROM dealer_access_list da, dealers d
    WHERE   da.dealer_id NOT IN (SELECT dealer_id FROM fixture_inventory)
    AND d.d开发者_开发知识库isabled = 0
    AND d.do_not_include IS NOT TRUE
    AND da.dealer_id = d.dealer_id

Thank you!

I have problems with joining more that two tables and using SQL special functions. I want to use CakePHP ORM and not plain SQL if possible.

Cake used to have the -! syntax for running SQL inside the find, which would have come in handy with your nested query. But with that deprecated, as far as I know, you might have to break it into 2 pieces.

I'm going to put my answer here using two queries, till someone can put up an alternative with the nested query

$dealers  = //get the dealer IDs in the table in the normal way. make sure it's an array of only the IDs

$opts = array(
  'fields' => array(
    'COUNT(DISTINCT(da.dealer_id)) AS count_dealer_id'
   ),
  'conditions' => array(
    'NOT' => array('da.dealer_id' => $dealers),
    'd.disabled' => 0,
    'd.do_not_include IS NOT TRUE',
  )
)
$results = $this->find('all', $opts); //assuming you're in the correct model

As long as you have the associations setup properly in the models, cake will figure out the correct joins and you don't have to worry about it.