Getting Unrelated Records From A Many To Many Relationship

Yii 1.1 General Discussion
1

I have two active records : Orders and Test

  • I have an association active record OrdersTests : orders_id, test_id as the fields.

The relations array for Orders:




public function relations()

{

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.

    return array(

        'tests' => array(self::MANY_MANY, 'Test', 'orderstests(orders_id, test_id)'),

    );

}

The relations array for Test:




public function relations()

{

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.

    return array(

        'orders' => array(self::MANY_MANY, 'Orders', 'orderstests(test_id, orders_id)'),

    );

}

To summarize an Order can have many tests and a Test can belong to many Orders.

I can easily get all the tests that belong to a particular order by using:




$order = Orders::model()->findByPk(1);

$tests = $order->tests;

But I also need to get all the tests which don’t belong to a particular order. i.e. $allTests ~ $order->tests

I can’t seem to find any relations query in Yii for that.

2

Do you need it to be a relation or would you settle for a method that returns those records when called?

EDIT:

Another option would be a parameterized scope in the Test model. Again, this would not work as a relation.

3

I don’t necessarily need it as a relation. A method would suffice. I was even thinking of resorting to a customized array_diff() method to return the difference of array of Test Objects.

But I could surely find something better with your help.

4

You could write this as a single query, but I think an approach like the following is clearer.

Firstly, I would consider adding the index attribute to your tests relation. The following assumes that the primary key of the Test model is ‘id’:




public function relations()

{

    return array(

        'tests' => array(self::MANY_MANY, 'Test', 'orderstests(orders_id, test_id)',

            'index' => 'id',

        ),

    );

}

That means that the array of models returned by that relation will be indexed by the test ID.

Then you could create a method like this in the Test model:




public static function getTestsNotInOrder($order)

{

    $excludedTestIds = array_keys($order->tests); // This makes use of the index attribute above


    $criteria = new CDbCriteria();

    $criteria->addNotInCondition('id', $excludedTestIds);


    return self::model()->findAll($criteria);

}

You would call it like this:




    $unattachedTests = Test::getTestsNotInOrder($order);

I’m sure you can come up with a better method name, and you may wish to write it as an instance method rather than a static method, but it should point you in the right direction.

5

Try the following:

Orders.php


?php 

class Orders extends CActiveRecord

{

   ...


//Returns instances of the class Test.php which don't belong to a particular  order with the given primary key.

   public function getNonRelationalTests($pk)

   {

       $db = Yii::app()->db;

       $subQuery = $db->createCommand()->select('test_id')->from('orderstests')

                   ->where('orders_id <> :pk');

       $query = $db->createCommand()->select()->from(Test::model()->tableName())

                   ->where('id IN (' . $subQuery->text . ')');

       return Test::model()->findBySql($query->text, array(':pk' => $pk));

   }


}
6

Your approach doesn’t work for a many-many relationship.

If Test 1 is attached to Order 1 and Order 2 and you run your getNonRelationalTests(1), your initial query will still return the joining table record which links Test 1 to Order 2.

In that scenario, Test 1 will be returned even though it is already attached to Order 1.

The solution that I’ve posted should work.

7

Hi, Keith!

Thanks for your notice. :)

I agree that in this case we need to get all the related tests at first.

This should work now:


public function getNonRelationalTests($pk)

{

       $db = Yii::app()->db;

       $subQuery = $db->createCommand()->select('test_id')->from('orderstests')

                   ->where('orders_id = :pk');

       $query = $db->createCommand->select('DISTINCT test_id')

                   ->from('orderstests')

                   ->where('test_id NOT IN (' . $subQuery->text . ')')

       $sql = $db->createCommand()->select()->from(Test::model()->tableName())

                   ->where('id IN (' . $query->text . ')');

       return Test::model()->findAllBySql($sql->text, array(':pk' => $pk));


}
8

why can’t you just do something like


<?php

// Test model

public function getTestsNotInOrder($orderID)

{

    $criteria = new CDbCriteria;

    $criteria->join = "JOIN orderstests ON test_id = id";

    $criteria->condition = "order_id != :orderID";

    $criteria->params = [":orderID" => $orderID]

    return self::model()->findAll($criteria);

}




// call it

Test::model()->getTestsNotInOrder($order_id);
9

Thanks all, This approach works best for me.

10

I tested it. There were issues at implementation of the solution by alirz23, I am not getting the unattached Tests, However by Keith’s approach I am getting the result perfectly. Not sure what is the issue with the join query.