How to re-write this query in Yii using model ?

Hi guys,

I am stuck with this query

            if (empty($email)) 


                # Can't have email campaign if no email

                $whereQuery = " AND print IS TRUE";


            $query = "

                SELECT *

                FROM Campaigns

                WHERE id NOT IN (

                    SELECT DISTINCT cs.campaign

                    FROM CampaignSubscriptions cs


               = :mortgage_id AND			

                        ((NOW() BETWEEN cs.when_starts AND cs.when_ends) OR

                        ((cs.when_ends IS NULL) AND (NOW() >= cs.when_starts))

                        ) AND cs.is_subscribed 		    


                AND is_mortgage IS NOT FALSE



            $campaigns = Campaign::model()->findAllBySql($query, array(':mortgage_id' => $mortgage_id));

This query finds campaigns that have not been subscribed. As you can see, Campaign and CampaignSubscription models are involved. I can not think of a way to do this using Yii model way.

If anyone knows, please share.


You can retrieve the query results as

$data= Campaign::model()->findAllBySql($query);


							foreach($data as $value=>$campaign)  




Hope this is what you want

Thanks for reply, but it is not what I am looking for.

I am looking for a way to get the same result without running that query (e.g. something likes Campaign::model()->getAvailableCampaigns())

In other words, is it possible to use model to get available campaigns without using findAllBySql

Well, let’s try to rewrite this query to join syntax

[sql]SELECT c.*

FROM Campaigns c LEFT OUTER JOIN CampaignSubscriptions cs

ON ( AND = :mortgage_id AND

  ((NOW() BETWEEN cs.when_starts AND cs.when_ends) OR ((cs.when_ends IS NULL) AND (NOW() >= cs.when_starts))) AND


WHERE cs.campaign IS NULL AND c.is_mortgage IS NOT FALSE AND print IS TRUE[/sql]

Now create Campaigns and CampaignSubscriptions models and add relation HAS_MANY

You request will look like

Campaigns::model()->with( 'subscriptions' => array( 'on' => /*... string here ...*/, 'params' => array(':mortgage_id' => /* xxx */) ) )->findAll(/* and here is criteria with where condition */)

cs.campaign IS NULL you can add in criteria or to ‘condition’ parameter of relation