Through relational query problem

Hi.

So, Im stuck with this problem for a while now, even after reading this a few times.

This is how my DB looks like:

tvshows

- id

- name

- description


seasons

- id

- show_id

- name


episodes

- id

- season_id

- name

- description

Basically, A TV show has a few seasons, and every single season has a few episodes.

I have set up my TVShow model relation like this:

public function relations()

	{

		return array(

			'seasons' => array(self::HAS_MANY, 'Season', 'show_id'),

			'episodes' => array(self::HAS_MANY, 'Episode', 'show_id', 'through' => 'seasons')

		);

	}

From a specific TV show I want to select all episodes that belongs to it.

So when I echo out

count(TVShow::model()->with('episodes')->findByPk(1)->episodes)

it returns 1, but it should return at least 100 episodes.

Yii generates this Query

SELECT `episodes`.`id` AS `t1_c0`, `episodes`.`season_id` AS `t1_c1`, `episodes`.`number` AS `t1_c2`, `episodes`.`name` AS `t1_c3`, `episodes`.`description` AS `t1_c4`, `episodes`.`views` AS `t1_c5`, `episodes`.`created` AS `t1_c6`, `episodes`.`updated` AS `t1_c7`

FROM `episodes` `episodes`

LEFT OUTER

JOIN `seasons` `seasons` ON (`seasons`.`show_id`=`episodes`.`id`)

WHERE (`seasons`.`show_id`=:ypl0)

ORDER BY number ASC

I noticed that it generated a wrong JOIN query, It needed to be

LEFT OUTER

JOIN `seasons` `seasons` ON (`seasons`.`id`=`episodes`.`season_id`)

I still dont have any clue after hours, please, any suggestions?

Thanks in advance

Hi opx,

  I think you do not have to use the following code.

 count(TVShow::model()->with('episodes')->findByPk(1)->episodes) 

You can use the code as follows

TVShow::model()->findByPk(1)->episodes

Because you have already defined the relation between tvshows and episodes

For getting record count, you can use the following code

public function relations()

        {

                return array(


                   'episodesCount' => array(

                                          self::STAT,

                                          'episodes',

                                           'show_id'

                                     ),

                )

        }

Thanks for your reply. But I’m not trying to get record count, I just used the count function to ensure that all episodes has loaded from the database, but it only loads one, because the generated query isn’t right.

Have you tried the following code

TVShow::model()->findByPk(1)->episodes

.

Yes, Ive tried that…

I think you are ignoring my SQL part, its because of the query it generated, and I dont know to create the appropriate query for it using CDbCritera.through property .

have you tried

'episodes' => array(self::HAS_MANY, 'Episode', 'season_id', 'through' => 'seasons')

Yes I have tried that, but I knew that it would never work because table seasons doesn’t have a column named season_id.

Anyway, I managed to get something else working, by adding a scope to my Episode model.

Maybe useful for others:

	public function fromShow($show_id = null)

	{

		if ($show_id != null) {

			$this->getDbCriteria()->mergeWith(

				array(

					 'with' => array(

						 'season' => array(

							 'on' => 'season.show_id = :show_id'

						 )

					 ),

					 'order' => 'updated DESC',

					 'limit' => 5,

					 'params' => array(':show_id' => $show_id)

				)

			);

		}


		return $this;

	}

I know this is a little late, but why not do a findAllByPk, instead of a findByPk?

count(TVShow::model()->with('episodes')->findAllByPk(1)->episodes)