union relation

I’m not sure if I going the right way about this.

I’ve got 3 tables : Event, Client and EventClient

EventClient records if a client attended an event and if they did, have they paid.

My problem is that I need a quick way of selecting the clients rather than the flow of add client, save, go back to grid etc.

I’m part way there by using ajax and toggling ‘attended’, here is the code for this:




// EventClient model

	public function getAttendedOptions(){

		return array('0' => 'Did not attend', '1' => 'Attended');

	}

	

	public function getAttendedOption(){

		if (array_key_exists($this->attended, $this->attendedOptions))

		{

			$options = $this->attendedOptions;

			return ($options[$this->attended]);

		}

		else

			return $this->attended;

	}	






// EventClient admin view - standard view with the following amendments

...

Yii::app()->clientScript->registerScript('ajaxattended', "

$('#event-client-grid a.ajaxattended').live('click', function() {

        $.fn.yiiGridView.update('event-client-grid', {

                type: 'POST',

                url: $(this).attr('href'),

                success: function() {

                        $.fn.yiiGridView.update('event-client-grid');

                }

        });

        return false;

});

");

...

	'columns'=>array(	

		array(

				'name'=>'attended',

				'type'=>'raw',

				'value'=>'CHtml::link($data->attendedOption, array("ajaxattended", "id"=>$data->id), array("class"=>"ajaxattended"));',

				'filter'=>$model->attendedOptions,

		),

...






// And this in the EventClientController

	public function actionAjaxAttended($id)

	{

			$model=$this->loadModel($id);

			$model->attended = (!$model->attended);

			$model->update();

	}



This works well for existing records - but I would like to list all clients and then toggle attended, which will (once the code has been written) create a record in the eventclient table if it doesn’t already exist.

So at the bottom of the event view I have




<div id="eventClient">

	<?php $this->renderPartial('_eventClients',array(

		'model'=>$model,

		'eventClients'=>$model->eventClients,

	)); ?>

</div>



and _eventClients.php is:




<?php


$sql = "

SELECT id, event_id, client_id, attended, paid, payment, comment, user_id

FROM event_client

WHERE event_id = {$model->id} AND user_id = {$model->user_id}

UNION

SELECT 0 as id, {$model->id} as event_id, c.id as client_id, 0 as attended, 0 as paid, 0 as payment, '' as comment, {$model->user_id} as user_id

FROM client as c

WHERE c.id not in (SELECT client_id FROM event_client as e WHERE e.id={$model->id} and e.user_id={$model->user_id})

ORDER BY client_id";


$count=Yii::app()->db->createCommand("SELECT COUNT(*) FROM sumba_client WHERE user_id={$model->user_id}")->queryScalar();


$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    'sort'=>array(

        'attributes'=>array(

             'client_id',

        ),

    ),

    'pagination'=>array(

        'pageSize'=>10,

    ),

));


$this->widget('zii.widgets.grid.CGridView', array(

	'dataProvider' => $dataProvider,

	'columns'=>array(

		'Event', 

		'Client',	

		'Attended', 

		'Paid', 

		'Payment', 

		'Comment',

	), 

));

?>



Which works but I’ve lost a lot of the functionality of the relations because I’m using SQL directly. I can’t use $model->client->name for example.

I’m wondering if its possible to have the union query in the relations model?

Many thanks, Russ

Being a bit dense, I should have used an outer join rather than a union. The question still stands though - can I get this into a relation in my model?




$sql = "

SELECT ifnull(ec.id,0) as id, 

ifnull(ec.event_id,{$model->id}) as event_id, 

c.id as client_id, 

ifnull(ec.attended,0) as attended, 

ifnull(ec.paid, 0) as paid,

ifnull(ec.payment, 0) as payment,

ifnull(ec.comment, '') as comment,

ifnull(ec.user_id,{$model->user_id}) as user_id

FROM event_client as ec

RIGHT OUTER JOIN client as c ON c.id=ec.client_id

";



1. Create models for EventClient and Client

2. Add a BELONGS_TO relationship to EventClient




public function relations()

{

  return array(

    'client' => array(self::BELONGS_TO, 'Client', 'client_id'),

  );

}



3. Define the criteria




  $crit = new CDbCriteria;

  $crit->select = array('ifnull(t.id,0) as id', '...', 'ifnull(client.id,0) as client_id', '...', '...');

  $crit->with = array('client');

  $crit->joinType = 'RIGHT OUTER JOIN'; // default is left join

  $crit->together = true;  // all in one query



4. Declare the column aliases as public properties of the model

5. Use the criteria




  $dp = new CActiveDataProvider('EventClient', array(

    'criteria' => $crit,

  ));

  ...

  ...



6. Optional: improve code structure (MVC)

Remark: default table aliases for event_client and client will be t and client respectively.

(partially tested)

/Tommy

Sorry for the delay I’ve been working on something else for a bit.

For 3 and 5, where would I put the code?

Thanks, Russ

Ah! I get it now, I’m replacing the search() in the model with the above criteria - brill!!!

For testing, just before the grid where you use it. Let us know if it works.

I’m not sure about 6 “improve structure”. Perhaps Probably create a method in the EventClient model, similar to the search() method in Gii-generated code.

/Tommy