Yii Framework Forum: union relation - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

union relation Rate Topic: ***** 1 Votes

#1 User is offline   Russell England 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 10-March 11
  • Location:Stourbridge, UK

Posted 03 May 2011 - 08:43 AM

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
0

#2 User is offline   Russell England 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 10-March 11
  • Location:Stourbridge, UK

Posted 03 May 2011 - 10:18 AM

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
";

0

#3 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 03 May 2011 - 01:10 PM

View PostRussell England, on 03 May 2011 - 10:18 AM, said:

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

This post has been edited by tri: 03 May 2011 - 01:17 PM

Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
1

#4 User is offline   Russell England 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 10-March 11
  • Location:Stourbridge, UK

Posted 25 May 2011 - 10:32 AM

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
0

#5 User is offline   Russell England 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 10-March 11
  • Location:Stourbridge, UK

Posted 25 May 2011 - 10:44 AM

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

#6 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 25 May 2011 - 10:55 AM

View PostRussell England, on 25 May 2011 - 10:32 AM, said:

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


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

This post has been edited by tri: 25 May 2011 - 11:01 AM

Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#7 Guest_ssshobhit_*

  • Group: Guests

Posted 21 March 2013 - 06:55 AM

$model = new bm_curriculum_resource;
$sql= 'SELECT res_com.* ,bm_curriculum_books_chapters.title AS t FROM (SELECT * FROM bm_curriculum_resource res
WHERE res.status = 1 AND res.class_id = 148
UNION ALL
SELECT * FROM bm_curriculum_resource_default res_def
WHERE res_def.status = 1
)res_com
LEFT JOIN bm_curriculum_books_chapters ON bm_curriculum_books_chapters.book_id=resource_id
AND bm_curriculum_books_chapters.status=1
ORDER BY res_com.resource_type ASC , res_com.createdate';

$resources = Yii::app()->db->createCommand($sql)->queryAll();
$count= Yii::app()->db->createCommand($sql)->queryScalar();
$dataProvider=new CSqlDataProvider($sql, array(
'totalItemCount'=>$count,
'sort'=>array(
'attributes'=>array(
'id',
),
),
'pagination'=>array(
'pageSize'=>10,
),
));


//$criteria = new CDbCriteria();
//$count=bm_curriculum_resource::model()->count($criteria);
//$pages=new CPagination($count);
//$pages->pageSize=10;
//$pages->applyLimit($criteria);
$this->render('index', array('model'=>$model,'resources'=>$resources,'pages' => $pages));
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users