Re: select all HABTM records not in join table

Like.

Jeremy Burns

Class Outfit

http://www.classoutfit.com

On 3 May 2013, at 03:03:11, lowpass <zijn.digital@gmail.com> wrote:

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.digital@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+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.