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+unsubscribe@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.
No comments:
Post a Comment