Need help with translating pure sql to ActiveRecord

I am trying to use AR/AQ for my complex project, and I always find it easier to write pure sql than trying to do the same with AR, but AR is nice and I would like to start using it for complex queries too, but I need some help, and I hope some one will be able to help me out.

I have 3 models with relations established between them. I need to execute ActiveRecord query that will do this:

SELECT COUNT(club_offer_id) 

FROM `club_offer_cpv` 

JOIN `club_offer`       ON club_offer_cpv.club_offer_id = club_offer.id

JOIN `club_territories` ON club_offer.club_id = club_territories.club_id

                        WHERE club_territories.territory_id = 1100000

                        AND club_offer_cpv.cpv_id = 66113000

My models and relations defined in them are:

ClubOfferCpv with relation to ClubOffer:

/**

 * Relation with ClubOffer model.

 * 

 * @return \yii\db\ActiveQuery

 */

public function getClubOffer()

{

    return $this->hasOne(ClubOffer::className(), ['id' => 'club_offer_id']);

}

ClubOffer model with relations to ClubOfferCpv and ClubTerritories:

/**

 * Relation with ClubOfferCpv model.

 * 

 * @return \yii\db\ActiveQuery

 */

public function getClubOfferCpv()

{

    return $this->hasMany(ClubOfferCpv::className(), ['club_offer_id' => 'id']);

}

/**

 * Relation with ClubTerritories model.

 * 

 * @return [type] [description]

 */

public function getClubTerritories()

{

    return $this->hasOne(ClubTerritories::className(), ['club_id' => 'club_id']);

}

And ClubTerritories model with relation to ClubOffer:

/**

 * Relation with ClubOffer model.

 * 

 * @return [type] [description]

 */

public function getClubOffer()

{

    return $this->hasOne(ClubOffer::className(), ['club_id' => 'club_id']);

}

Can anyone help me with this please, because it will help me a lot in understanding the way of using AR and Active Query. Thanks <3

May I ask why bother with rewriting this query to AR? I mean you clearly need only one field/result, which is count… I think that also performance-wise would be better to just stick with something like this:

$connection->createCommand("SELECT COUNT(club_offer_id) 

FROM `club_offer_cpv` 

JOIN `club_offer`       ON club_offer_cpv.club_offer_id = club_offer.id

JOIN `club_territories` ON club_offer.club_id = club_territories.club_id

                        WHERE club_territories.territory_id = :terrid

                        AND club_offer_cpv.cpv_id = :cpvid")->bindValues([":terrid"=>1100000, ":cpvid"=>66113000])->queryOne();

I have more complex queries that are similar to this, and they return resources. This would be good example to me to learn how to do it AR way. And I would really like to be consistent in code and use only AR.

Oh, now I saw that you did DAO with values binding, thanks this is also good for me, but would really like to see AR. Thank you

And I forgot, I would also like to know how to write this query in AR, because I would like to use eager loading that comes with it, whenever that is possible.

Then if i were you i would try something like this:

ClubOfferCpv::find()->joinWith(['clubOffer','clubOffer.clubTerritories'])->andWhere(['territory_id'=>1100000

, 'cpv_id'=>66113000])->count();

Not tested and i’m not an expert, but give it a try…