CODEWITHSUNDEEP
phpyii
Anna
Anna

Reputation: 21

HowTo select all models based on related model in Yii

I have the following tables:

CREATE  TABLE match (
    id INT NOT NULL PRIMARY KEY,
    home_team_id INT,
    away_team_id INT,
    ...
)

CREATE  TABLE scorer (
   id INT NOT NULL PRIMARY KEY,
   match_id INT,
   player_id INT,
   ....
)

In Match model I defined a relation something like this:

class Match extends CActiveRecord {
    public function relations() {
        return array(
            'scorers' => array(
                self::HAS_MANY,
                'Scorer',
                'match_id',
             ),
             ...
        );
    }
}

How can I get all Match models that have at least one scorer?

Upvotes: 0

Views: 158

Answers (2)

deacs
deacs

Reputation: 4309

From this article on the forum:

$matches = Match::model()->with(array('scorers'=>array('joinType'=>'INNER JOIN','together'=>true)))->findAll();

It's untested, though it looks right. Give it a bash.

Upvotes: 1

soju
soju

Reputation: 25312

If you are concern about performance issues you should not use :

$matches = Match::model()->with(array('scorers'=>array('joinType'=>'INNER JOIN','together'=>true)))->findAll();

Simply because this will fetch, for each match, all corresponding scorers.

If you don't need to get scorers data, you should try to add this in your Match model (just an example) :

public function findAllHavingScorer()
{
    $matchAlias = $this->tableAlias;
    $scorerTable = Scorer::model()->tableName();

    return $this->findAll(array(
        'condition'=>"EXISTS (SELECT * FROM $scorerTable WHERE $scorerTable.post_id=$matchAlias.id)",
    ));
}

After you just have to use this :

$matches = Match::model()->findAllHavingScorer();

Upvotes: 0

Related Questions