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

Yii 1.1 General Discussion
1

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

                    WHERE		

                        cs.mortgage = :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

                $whereQuery

            ";


            $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.

Thanks.

2

You can retrieve the query results as




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

							$data=CHtml::listData($data,'campaign_id','campaign');

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

							{

								$campaigns=CHtml::encode($campaign);

							}

Hope this is what you want

3

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

4

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

[sql]SELECT c.*

FROM Campaigns c LEFT OUTER JOIN CampaignSubscriptions cs

ON (c.id=cs.campaign AND

  cs.mortgage = :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)

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