Relation scope to test existence of related records ("EXISTS")

When one needs to select a record in a table that do not have related records through a relation, it is generally suggested to use a subquery. Personnaly, I prefer a high level approach, and I have a method to do it with scopes.

Below is the sample code. Basically, the example implements a scope called 'not_exclusive'. The purpose of the scope is to find records in the current table that do not have a 'parent_relations' set indicating that there is an exclusive ownership. The exclusive ownership condition is defined in a scope of the parent_relations' model type. When using the 'not_exclusive' scope like this:

Entity::model()->not_exclusive()->findAll();

only the records that do not have a 'parent_relations' record will be returned. Indeed, the 'not_exclusive' relation required that the resulting 'LEFT JOIN' has a NULL value for the primary key of the 'parent_relations' record. If such a record would exist, the primary key will not be NULL. Further, the 'group' constraint makes sure that at most one related record will be kept and helps make it all work.

public function scopes()
    {
            $ds=$this->getDbConnection()->getSchema();
            $table=$this->getTableAlias(false,false);
            $entity_id = $ds->quoteColumnName("$table.entity_id");
            $scopes=array(
                    'not_exclusive'=>array(
                            'with' => array(
                                'parent_relations' => array(
                                    'together' => true, // Required to include parent_relations when counting.
                                    'alias' => 'exl',  // Alias for use in "ISNULL" test + avoids name clash.
                                    'scopes'=>'hasexclusiveaccess',  // Scope to apply to the 'parent_relations'
                                    'joinType'=>'LEFT JOIN', // RIGHT is allowed to be missing (needed!).
                                    //'select'=>'',
                                )
                            ),
                            'group' => $entity_id,  // Primary key of this table -> only one entry of current table for each record.
                            'having' => 'ISNULL('.$ds->quoteColumnName("exl.".ParentChild::PARENT_ID).')',  // Test on primary key of relation -> requires that relation is missing (use opposite test if you require that the relation exists).
                    ),
            );
        return $scopes;
    }

1 0
2 followers
Viewed: 13 749 times
Version: 1.1
Category: How-tos
Written by: le_top
Last updated by: le_top
Created on: Mar 28, 2015
Last updated: 8 years ago
Update Article

Revisions

View all history

Related Articles