DB relation issue

After reading the “The Definitive Guide to Yii” on relational active records, I’m still confused as to how model relationships work and hoping someone can shed some light on the matter.

To help understand what I’m trying to acheive the following query will return exactly the result I’m trying to get via DAO (Database Access Objects).

SELECT Widgets_Table.id, Widgets_Table.name

  FROM Widgets_Table

  INNER JOIN Group_Has_Widget_Table ON Group_Has_Widget_Table.widget_id = Widgets_Table.id

  INNER JOIN Group_Table ON Group_Table.id = Group_Has_Widget_Table.group_id

  WHERE Group_Table.id = '1'

I have 3 tables. Widgets_Table, Groups_Table, Group_Has_Widget_Table with the following columns.

(I’ve simplified the tables for this post)

[Widgets_Table]

id (int)(key)

name (string)


[Groups_Table]

id (int)(key)

name (string)


[Group_Has_Widget_Table]

group_id (int)

widget_id(int)

My Yii DAO configuration is as follows:

The relation in my Widgets Model is:

/**

 * @return array relational rules.

 */

public function relations()

{

  return array(

    'groups'=>array(

       self::MANY_MANY,

       'Groups',

       'Group_Has_Widget_Table(widget_id, group_id)'

    ),

  );

}

The relation in my Groups Model is:

/**

 * @return array relational rules.

 */

public function relations()

{

  return array(

    'widgets'=>array(

	self::MANY_MANY,

	'Widgets',

	'Group_Has_Widget_Table(group_id, widget_id)',

    ),

  );

}

And the call in the controller is:

Widgets::model()->with(array(

	'groups'=>array(

	// I don't want to select groups

	'select'=>false,

	// but want to get only widgets that belong to the group with id 1

	'joinType'=>'INNER JOIN',

	'condition'=>'groups.id=1',

	),

))->findAll($criteria);

But the above call returns all the widgets regardless of the groups.id=1 condition.

To summarise, the expected result is for all records in the Widgets_Table to be returned only if it belongs to the group that has the Group_Table.id equal to 1.

Works for me ($criteria omitted). What do you have in the criteria? What SQL is generated?

/Tommy

Hi tri, Thanks for the assistance…

After some further testing the problem does seem to be with the $criteria. If no criteria is passed I get the expected result. (even though the declared criteria has no properties).

$criteria declaration and passing is as follows:

$criteria = new CDbCriteria();


$widgets = Widgets::model()->valid()->with(array(

					  'groups'=>array(

					  // I don't want to select groups

					  'select'=>false,

					  // but want to get only widgets that belong to the group with id 1

					  'joinType'=>'INNER JOIN',

					  'condition'=>'groups.id=1',

					  ),

			))->findAll($criteria);

and the sql result in the log for the above is:

 SELECT [t].[id] AS [t0_c0] FROM [dbo].[Widgets_Table] [t] INNER

JOIN [dbo].[Widget_Has_Group_Table] [groups_groups] ON

([t].[id]=[groups_groups].[widget_id]) INNER JOIN [dbo].[widget_group]

[groups] ON ([groups].[id]=[groups_groups].[group_id])  WHERE ([t].[id] IN

(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)) AND (groups.id=1)

if I remove $criteria I get the correct following sql result:

SELECT [t].[id] AS [t0_c0], [t].[Name] AS [t0_c1],

[t].[Description] AS [t0_c2], [t].[Version] AS [t0_c3], [t].[Type] AS

[t0_c4], [t].[CustomerDatabaseName] AS [t0_c5], [t].[DeveloperID] AS

[t0_c6], [t].[Keywords] AS [t0_c7], [t].[Active] AS [t0_c8], [t].[Filesize]

AS [t0_c9], [t].[Price] AS [t0_c10], [t].[DateAdded] AS [t0_c11],

[t].[Filename] AS [t0_c12], [t].[ExpiryDate] AS [t0_c13] FROM

[dbo].[Widgets_Table] [t]  INNER JOIN [dbo].[Group_Has_Widget_Table] [groups_groups]

ON ([t].[id]=[groups_groups].[widget_id]) INNER JOIN [dbo].[widget_group]

[groups] ON ([groups].[id]=[groups_groups].[group_id])  WHERE (groups.id=1)

From the 2 results I can see that when $criteria comes into play (with or without criteria properties) its significantly different in both the SELECT and WHERE clause.

You should probably also note that I’m using the CPagination class… so the entire code call in my controller is as follows:

/**

	 * This is the default 'index' action that is invoked

	 * when an action is not explicitly requested by users.

	 */

	public function actionIndex()

	{				

		$criteria = new CDbCriteria();

		if(!empty($_GET['filter'])) {

			$term = $_GET['filter'];

			//$criteria->condition='Active=:widgetActive';

			//$criteria->params=array(':widgetActive'=>'Y');

			$criteria->compare('Name',$term, true, 'OR');

			$criteria->compare('Description',$term, true, 'OR');

			$criteria->compare('Keywords',$term, true, 'OR');

		}

		

		

		$count=Widgets::model()->count($criteria);

		$pages=new CPagination($count);

	

		// results per page

		$pages->pageSize=10;

		$pages->applyLimit($criteria);

		$widgets = Widgets::model()->with(array(

					  'groups'=>array(

					  // I don't want to select groups

					  'select'=>false,

					  // but want to get only widgets that belong to the group with id 1

					  'joinType'=>'INNER JOIN',

					  'condition'=>'groups.id=1',

					  ),

			))->findAll($criteria);

		




		

		if($widgets===null)

		{

			throw new CHttpException(404,'No widgets found');

		}		

					

		$this->render('index', array(

		'widgets' => $widgets,

			 'pages' => $pages

		));					

		 

	}

Tri, or anyone else know why the SQL generated is different?

If the $criteria is an instance of CDbCriteria with no conditions or properties, shouldn’t it be the same as not passing $criteria at all?

I don’t know the solution to your problem. It may be related to the fact that the LIMIT (MySql syntax) applied when you add the pagination, will force generation of more than one query. The SQL example looks very strange to me. It might be something with the MS SQL Schema classes.

What will be the result if you just remove the pagination and leave everything else in?

/Tommy

Wow, It works as expected with no pagination. Any ideas on how I could fix this without debugging the pagination class?

FYI - MSSQL combined with PHP is an ugly combination but the integration with Yii has been ok upto this point. I did have datatype issues in generating and querying from the models but they have now been resolved.

Check if adding this to the criteria will work as expected

$criteria->together=true;

Read the last paragraph in this section of the guide for reference.

/Tommy

Don’t I feel special… This paragraph, answered my question exactly…

Thanks mate… Love your work!