Yii Framework Forum: Selecting join table with mant to many? - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

Selecting join table with mant to many? Rate Topic: ***** 3 Votes

#41 User is offline   Athari 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 22-October 11
  • Location:Kazan, Russia

Posted 30 October 2011 - 06:52 AM

View PostSDavenport, on 29 March 2011 - 09:05 AM, said:

Tommy, I took your idea to iterate over unitPersons and it worked great. Thanks!

UnitPerson model relations
-------------------
public function relations() {
	return array(
		'unit' => array(self::BELONGS_TO, 'Unit', 'unit_id'),
		'person' => array(self::BELONGS_TO, 'Person', 'person_id'),
	);
}


foreach ($data->unitPersons as $unitPerson)
{
   echo $unitPerson->person->last_name . ' ' . $unitPerson->type_id . '<br>';
}



Relying on BELONGS_TO relations after querying 'with-through' turned out to be a bad idea. These properties aren't cached during the query, so every access means additional query for every row in the relations table which is unacceptable.

My solution was to add 'index' property to the first relation of the first table, set to the field which is a foreign key of the second table, so that rows queried from the relations table are indexed by IDs of the second table (as rows from the relations table and second table are one-to-one in this query). This way, after the request, I can iterate through rows of the second table and get corresponding rows from the relations table.

[ First.php ]

    public function relations()
    {
        return array(
            'rels' => array(
                self::HAS_MANY, 'Rel.php', 'firstId',
                'index' => 'secondId'),
            'seconds' => array(
                self::HAS_MANY, 'Second.php', 'secondId',
                'through' => 'rels'),
        );
    }

    public function getWithSeconds($id)
    {
        $first = $this->findByPk($id, array('with' => array('rels', 'seconds')));
        foreach ($first->seconds as $second)
            $second->propertyFromRel = $first->rels[$second->id]->property;
        return $first;
    }

“Today is the first day of the rest of your life”Personal website: Snow Lands
0

#42 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 10 November 2011 - 09:07 AM

I wrote an extension to get data from a join table into properties of the related objects:



When loading a related object through a join table, I want to be able to load (eager or lazy) data from the join table into properties of the related objects.

class Viewer extends CActiveRecord {

  public function relations() {
    return array(
      'movies' => array(self::MANY_MANY, 'Movie',
        'viewer_watched_movie(viewer_id, movie_id)',
        'select' => 'viewer_watched_movie.liked',
      ),
    );
  }

  // An example of lazy loading the liked property into Movie objects 
  public function viewerReviews($id) {
    $viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->movies as $m)
      echo $viewer->name . ($m->liked ? ' liked ' : ' didn’t like ') . $m->title;
  }

}
// and perhaps we need to declare liked as a property of Movie


The extension does this (with slightly different relation specs) but I think something like the above should be part of the framework. There's nothing exotic about data that logically belongs to a relation between objects rather than to the objects. In SQL RDBMS they are conventionally put in a join table. It should be easy to access the data.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#43 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 10 November 2011 - 09:30 AM

fsb

Quote

I wrote an extension to get data from a join table into properties of the related objects

For what? Framework already have this feature:
http://www.yiiframew...ry-with-through
No good, no bad, only consequence.
0

#44 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 10 November 2011 - 12:51 PM

View Postcreocoder, on 10 November 2011 - 09:30 AM, said:

fsb

For what? Framework already have this feature:
http://www.yiiframew...ry-with-through


I tried applying that to my problem and failed. I must have gone through that section of the guide half a dozen times. I'd be grateful if you could help me get the relation definitions right.

If, in the above example if I redefine the relations in Viewer thus:

class Viewer extends CActiveRecord {

  public function relations() {
    return array(
      'moviesJoin' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
      'movies' => array(self::MANY_MANY, 'Movie', 'viewer_watched_movie(viewer_id, movie_id)',
        'through' => 'moviesJoin',
      ),
    );
  }

  // An example of lazy loading the liked property into Movie objects 
  public function viewerReviews($id) {
    $viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->movies as $m)
      echo $viewer->name . ($m->liked ? ' liked ' : ' didn’t like ') . $m->title;
  }

}

Then I get CException: Property "Movie.liked" is not defined.

If I try to specify that the liked column should be loaded:
      'movies' => array(self::MANY_MANY, 'Movie', 'viewer_watched_movie(viewer_id, movie_id)',
        'through' => 'moviesJoin', 'select' => 'viewer_watched_movie.liked',
      )

Then the error is CDbException: Active record "Movie" is trying to select an invalid column "viewer_watched_movie.liked". The following 'select' specs all throw the same exception.
'select' => 'liked'
'select' => 'moviesJoin.liked'
'select' => 'moviesJoin_moviesJoin.liked'

Putting 'select' options in the 'moviesJoin' relation causes a PHP error: array_diff(): Argument #1 is not an array CActiveRecord.php:1969

Full context of this code is in the GitHub repo I previously mentioned, including EER chart, fixtures and unit test.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#45 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 10 November 2011 - 02:27 PM

fsb
There is no 'through' option for MANY_MANY. 'Through' is HAS_MANY/HAS_ONE option for replace MANY_MANY in situations when you need to access to fields in joining table.

First. You need model for 'viewer_watched_movie' table. Than you can use 'through':
public function relations() {
    return array(
      'moviesJoin' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'moviesJoin'),
);


Note that FK format for last relation(movies) definition changed if you use 1.1.9-dev.
No good, no bad, only consequence.
0

#46 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 10 November 2011 - 04:41 PM

View Postcreocoder, on 10 November 2011 - 02:27 PM, said:

fsb
There is no 'through' option for MANY_MANY. 'Through' is HAS_MANY/HAS_ONE option for replace MANY_MANY in situations when you need to access to fields in joining table.

First. You need model for 'viewer_watched_movie' table. Than you can use 'through':
public function relations() {
    return array(
      'moviesJoin' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'moviesJoin'),
);


Note that FK format for last relation(movies) definition changed if you use 1.1.9-dev.


OK.

Now, how do I access the value of the liked column in the join table?

Assuming
$viewer = Viewer::model()->findByPk($id);
$movie = $viewer->movies[0];

how do I tell if $viewer liked $movie?
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#47 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 10 November 2011 - 04:57 PM

Quote

Now, how do I access the value of the liked column in the join table?

Simple
$moviesJoin = $viewer->moviesJoin[0];

For max effective you can do that:
$viewer = Viewer::model()->with(array('movies','moviesJoin'))->findByPk($id);

No good, no bad, only consequence.
0

#48 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 10 November 2011 - 05:28 PM

View Postcreocoder, on 10 November 2011 - 04:57 PM, said:

Simple
$moviesJoin = $viewer->moviesJoin[0];

For max effective you can do that:
$viewer = Viewer::model()->with(array('movies','moviesJoin'))->findByPk($id);


How do I know that $viewer->moviesJoin[0] corresponds to $viewer->movie[0] ? I considered this but didn't dare assume that the indexes to these arrays have semantic value.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#49 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 11 November 2011 - 06:12 AM

I wonder if you could not take another approach instead. Say we have 3 models, Viewer, Movie and Loan (which represents the link table)

// in Viewer
'loans' =>array(self::HAS_MANY,'Loan','id_viewer'),

// in Loan (which is the link table)
'viewer'=>array(self::BELONGS_TO,'Viewer'),
'movie'=>array(self::BELONGS_TO,'Movie'),

// Then use a criteria / provider like this:
$criteria=new CDbCritera(array(
    'with'=>'loans.movie',
));
$provider=new CActiveDataProvider('Viewer',array(
    'criteria'=>$critieria,
));

// You then can access the data in a list view like:
foreach($data->loan as $loan)
{
    echo "Movie: ". $loan->movie->name;
    echo $loan->active ? ' (active)' : ' (not active)';
}

0

#50 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 11 November 2011 - 07:35 AM

View PostMike, on 11 November 2011 - 06:12 AM, said:

I wonder if you could not take another approach instead. Say we have 3 models, Viewer, Movie and Loan (which represents the link table)

My example had `viewed` as the join table with column `liked`. I think your `loan` is equivalent.

Quote

// in Viewer
'loans' =>array(self::HAS_MANY,'Loan','id_viewer'),

// in Loan (which is the link table)
'viewer'=>array(self::BELONGS_TO,'Viewer'),
'movie'=>array(self::BELONGS_TO,'Movie'),

// Then use a criteria / provider like this:
$criteria=new CDbCritera(array(
    'with'=>'loans.movie',
));
$provider=new CActiveDataProvider('Viewer',array(
    'criteria'=>$critieria,
));

// You then can access the data in a list view like:
foreach($data->loan as $loan)
{
    echo "Movie: ". $loan->movie->name;
    echo $loan->active ? ' (active)' : ' (not active)';
}


It works for part of the problem. When filling a zii.CGridView display of viewers you might want all viewers, not just those that appear in the join table.

But at least it doesn't use the undocumented feature that two independently-gotten arrays of AR objects are index aligned. Creocoder's suggestion is especially worrying since he hinted this stuff may be changing in 1.1.9.

So far I still prefer:
$viewers = Viewer::model()->with('watched.movie')->findAll();
foreach ($viewers as $viewer) 
  foreach ($viewer->_moviesJoin as $j) 
    echo $j->viewer->name . ($j->liked ? ' liked ' : ' didn’t like ') . $j->movie->title . PHP_EOL;


Which is what my CActiveRecord extension uses. It also offers a lazy version. And allows access in the AR idiom, e.g.:

foreach ($viewers as $viewer) 
  foreach ($viewer->movies as $movie) 
    echo $viewer->name . ($movie->liked ? ' liked ' : ' didn’t like ') . $movie->title . PHP_EOL;

Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#51 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 11 November 2011 - 08:49 AM

View Postfsb, on 11 November 2011 - 07:35 AM, said:

It works fo part of the problem. When filling a zii.CGridView display of viewers you might want all viewers, not just those that appear in the join table.

I can't follow - you do get all viewers and you join all loans/viewed including movies. If a viewer has not viewed a movie, then the relation will be an empty array.

View Postfsb, on 11 November 2011 - 07:35 AM, said:

But at least it doesn't use an undocumented feature of two independently-gotten arrays of AR objects being index aligned.


It is documented - but maybe the docs could be enhanced with better examples.


View Postfsb, on 11 November 2011 - 07:35 AM, said:

So far I still prefer:
$viewers = Viewer::model()->with('watched.movie')->findAll();
foreach ($viewers as $viewer) 
  foreach ($viewer->_moviesJoin as $j) 
    echo $j->viewer->name . ($j->liked ? ' liked ' : ' didn’t like ') . $j->movie->title . PHP_EOL;


Which is what my CActiveRecord extension uses. It also offers a lazy version. And allows access in the AR idiom, e.g.:


I can't really see the difference. You select all viewers and join all watched.movies. Why would you need an extension for this?
0

#52 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 123
  • Joined: 09-January 11

Posted 11 November 2011 - 09:39 AM

You're quite right. I misunderstood your example. Your method is the same as the one I put in my CActiveRecord extension.

What I think lacks documentation is as follows.

If Viewer has:
public function relations() {
    return array(
      'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'watched'),
);
then I think creocoder is saying we can access:
$viewer->watched[3]->liked;
$viewer->movies[3]->title;
and know that these two data correspond to the same movie.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
3

#53 User is offline   c@cba 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 02-December 11

Posted 20 February 2012 - 11:50 PM

View Postfsb, on 11 November 2011 - 09:39 AM, said:

You're quite right. I misunderstood your example. Your method is the same as the one I put in my CActiveRecord extension.

What I think lacks documentation is as follows.

If Viewer has:
public function relations() {
    return array(
      'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'watched'),
);
then I think creocoder is saying we can access:
$viewer->watched[3]->liked;
$viewer->movies[3]->title;
and know that these two data correspond to the same movie.


Hi thefsb, I read your wiki article about this topic too, which made me think...
To get rid of this 'indexing trick', we could do the following:

'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id', 'index'=>'movie_id'),
'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'watched', 'index'=>'id'),

This way we make sure that the 'watched' entries, as well as the 'movies' entries are both indexed with the id of the movie.
Then we can use them like this (tested, works):
foreach($viewer->movies as $id=>$movie) {
	echo $viewer->name . 'watched ' . $movie->title . 'and ' . ($viewer->watched[$id]->liked ? ' liked ' : ' didn’t like ') . ' it.';
}


Best regards...
1

#54 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 27 March 2012 - 12:26 PM

Quote

This way we make sure that the 'watched' entries, as well as the 'movies' entries are both indexed with the id of the movie.

This is not necessary. $viewer->moviesJoin[0] corresponds to $viewer->movie[0] as $viewer->moviesJoin[n] corresponds to $viewer->movie[n].

Quote

I considered this but didn't dare...

You need to be more decisive and everything will be okay. :)
No good, no bad, only consequence.
1

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users