Solved! I just tried yet another (desperate) way -- fetch the item_ids from
the join table, then all ids from main table, use array_diff() and a
regular find using the result, etc -- and this time I paid a bit more
attention to the "execution time" error message. I'd noticed earlier that
it gave a line # in Debugger.php but didn't stop to consider what that
meant. Of course, the answer was to set debug to 0!
I hope this helps someone, somehow, someday. For completeness, here's the
entire method:
public function fetchOrphans() {
Configure::write('debug', 0);
$this->unBindModel(array('hasAndBelongsToMany' => array('Category')));
$this->bindModel(array(
'hasOne' => array(
'CategoriesItem' => array(
'foreignKey' => false,
'conditions' => array(
'CategoriesItem.item_id = Item.id'
)
)
)
));
return $this->find(
'all',
array(
'conditions' => array(
'CategoriesItem.category_id IS NULL'
)
)
);
}
On Thu, May 2, 2013 at 6:44 PM, lowpass <zijn.digi...@gmail.com> wrote:
> Cake 2.x
> Item <- habtm -> Category
>
> I'm trying to select all uncategorised Items. There are only 723 records
> in the items table, 188 of which are uncategorised. I know this last fact
> because I can run the following query in mysql client:
>
> SELECT * FROM items LEFT JOIN categories_items AS ci ON ci.item_id =
> items.id WHERE ci.category_id IS NULL
>
> This runs very quickly. Everything I've tried through Cake results in
> max_execution_time running out. Disabling it just runs down the memory.
>
> Here are some things I've tried already, all with and without recursive ==
> -1, models bound & unbound, etc. Can anyone spot a mistake? Or think of a
> reason why this isn't working?
>
>
> $this->unBindModel(array('hasAndBelongsToMany' => array('Category')));
>
> $this->bindModel(array(
> 'hasOne' => array(
> 'CategoriesItem' => array(
> 'foreignKey' => false,
> 'conditions' => array(
> 'CategoriesItem.item_id = Item.id'
> )
> )
> )
> ));
>
> return $this->find(
> 'all',
> array(
> 'conditions' => array(
> 'CategoriesItem.category_id IS NULL'
> )
> )
> );
>
>
> // and ...
> return $this->find(
> 'all',
> array(
> 'conditions' => array(
> 'NOT EXISTS (SELECT category_id FROM categories_items WHERE item_id =
> Item.id)'
> )
> )
> );
>
>
> // and ...
> return $this->find(
> 'all',
> array(
> 'conditions' => array(
> 'CategoriesItem.category_id IS NULL'
> ),
> 'joins' => array(
> array(
> 'table' => 'categories_items',
> 'alias' => 'CategoriesItem',
> 'type' => 'left',
> 'conditions' => array(
> $this->alias.'.'.$this->primaryKey => 'CategoriesItem.item_id'
> )
> )
> )
> )
> );
>
>
> // and even ...
> $query = 'SELECT * FROM items'
> . ' LEFT JOIN categories_items AS ci'
> . 'ON ci.item_id = items.id '
> . ' WHERE ci.category_id IS NULL';
> return $this->query($query);
>
>
> // desperate ...
> $ids = array_keys($this->CategoriesItem->find(
> 'list',
> array(
> 'fields' => array('item_id', 'category_id')
> )
> ));
>
> return $this->find(
> 'all',
> array(
> 'recursive' => -1,
> 'conditions' => array(
> 'NOT' => array('Item.id' => $ids)
> )
> )
> );
>
> I apologise for the long post.
>
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
