CakePHP finds elements without writing HABTM relationships

Simply put: Tags HABTM Documents

Is there a way to find all Tags

unrelated Document

?

I started with this:

$freeTags = $this->Tag->find('all', array(
            'conditions' => array(
            ),
            'contain' => array(
                'Document'
            ),
            'recursive' => -1
        ))

      

but I have no idea how to get a request like this:

SELECT * FROM tags WHERE id NOT IN (SELECT tag_id FROM documents_tags)

      

My CakePHP version is 2.3

EDIT: Final solution, method in Tag

model

    $db = $this->getDataSource();
    $subQuery = $db->buildStatement(
            array(
                'fields' => array('DocumentsTag.tag_id'),
                'table' => $db->fullTableName($this->DocumentsTag),
                'alias' => 'DocumentsTag',
                'limit' => null,
                'offset' => null,
                'joins' => array(),
                'conditions' => null,
                'order' => null,
                'group' => null
            ), $this->DocumentsTag
    );
    $subQuery = ' Tag.id NOT IN (' . $subQuery . ') ';
    $subQueryExpression = $db->expression($subQuery);

    $conditions[] = $subQueryExpression;

    $freeTags = $this->find('all', compact('conditions'));

      

+3


source to share


1 answer


How CookBook :: Sub queries you could make

$db = $this->User->getDataSource();
$subQuery = $db->buildStatement(
    array(
        'fields'     => array('"DocumentsTag"."tag_id"'),
        'table'      => $db->fullTableName($this->DocumentsTag),
        'alias'      => 'DocumentsTag',
        'limit'      => null,
        'offset'     => null,
        'joins'      => array(),
        'conditions' => null,
        'order'      => null,
        'group'      => null
    ),
    $this->DocumentsTag
);
$subQuery = ' "Tag"."id" NOT IN (' . $subQuery . ') ';
$subQueryExpression = $db->expression($subQuery);

$conditions[] = $subQueryExpression;

$this->User->find('all', compact('conditions'));

      



I hope this can be helpful for you.

+1


source







All Articles