Yii Framework Forum: Please Help Me: How To Join A Table From Other Database In Cgirdview? - Yii Framework Forum

Jump to content

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

Please Help Me: How To Join A Table From Other Database In Cgirdview?

#1 User is offline   Vi Quang Hòa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 06-June 09

  Posted 13 October 2013 - 11:00 PM

How to join a table from other database?

protected/models/User.php ( db = db_sem )

Attached File  table_user.jpg (69.24K)
Number of downloads: 8

protected/models/Orders.php ( default db = shopphanmem )
Attached File  table_orders.jpg (313.03K)
Number of downloads: 9


When running the error 500: CDbCommand failed to execute the SQL statement: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'shopphanmem.qc_user' doesn't exist.

Please help me!

Thanks!
0

#2 User is offline   Sergey Zhuravel 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 13-October 13
  • Location:Ukraine

Posted 14 October 2013 - 12:18 AM

Hi, Please check that qc_user table exists in the shopphanmem DB. Also verify your configuration of the shopphanmem db in your config file (here you can find a good guide how to configure two DBs in YII - for example check if the configuration is correct and user has access to shopphanmem's tabls. Also check the case of db/table names, sometimes it's common issue for case sensitive OSs.
0

#3 User is offline   Vi Quang Hòa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 06-June 09

Posted 14 October 2013 - 02:23 AM

View PostSergey Zhuravel, on 14 October 2013 - 12:18 AM, said:

Hi, Please check that qc_user table exists in the shopphanmem DB. Also verify your configuration of the shopphanmem db in your config file (here you can find a good guide how to configure two DBs in YII - for example check if the configuration is correct and user has access to shopphanmem's tabls. Also check the case of db/table names, sometimes it's common issue for case sensitive OSs.


Thanks!
0

#4 User is offline   Sergey Zhuravel 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 13-October 13
  • Location:Ukraine

Posted 14 October 2013 - 10:55 AM

Your welcome. Please update this thread when you resolve the issue. Or provide additional/new information if you need futher help for your issue.
0

#5 User is offline   Vi Quang Hòa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 06-June 09

  Posted 17 October 2013 - 10:41 PM

View PostSergey Zhuravel, on 14 October 2013 - 10:55 AM, said:

Your welcome. Please update this thread when you resolve the issue. Or provide additional/new information if you need futher help for your issue.

Please view result in attach file.
------------
Because I use table {{user}} for multiple websites, so I placed it in the database db_sem and table {{orders}} so I placed it in the database db. In protected/config/main.php my configuration:

'components' => array(
'[b]db[/b]'=>require(dirname(__FILE__).'/db.php'),	
'[b]db_sem[/b]'=>require(dirname(__FILE__).'/db_sem.php'),	
....
)


I have solved the following:
1. in relations function of models/Orders.php
public function relations()
	{
		return array(
			'user' => array(self::BELONGS_TO, 'OrdersUser', 'user_id'),
            'info_' => array(self::HAS_ONE, 'OrderInfoJoin', 'order_id'),			
		);
	}


2. in search function of models/Orders.php
public function search()
	{
		$criteria=new CDbCriteria;

		$criteria->compare('order_id',$this->order_id);
		$criteria->compare('code',$this->code,true);
		$criteria->compare('subtotal',$this->subtotal);
		$criteria->compare('user_type',$this->user_type);
		$criteria->compare('user_code',$this->user_code,true);
		//$criteria->compare('user_id',$this->user_id);
		$criteria->compare('t.user_id', $this->website);
		
		$criteria->compare('fullname',$this->fullname,true);
		$criteria->compare('address',$this->address,true);
		$criteria->compare('email',$this->email,true);
		$criteria->compare('phone',$this->phone,true);
		$criteria->compare('province',$this->province);
		$criteria->compare('cmtnd',$this->cmtnd);
		$criteria->compare('csn_gender',$this->csn_gender);
		$criteria->compare('csn_info',$this->csn_info);
		$criteria->compare('csn_fullname',$this->csn_fullname,true);
		$criteria->compare('csn_address',$this->csn_address,true);
		$criteria->compare('csn_email',$this->csn_email,true);
		$criteria->compare('csn_phone',$this->csn_phone,true);
		$criteria->compare('csn_province',$this->csn_province);
		$criteria->compare('csn_cmtnd',$this->csn_cmtnd);
		$criteria->compare('csn_yahooid',$this->csn_yahooid,true);
		$criteria->compare('notes',$this->notes,true);
		$criteria->compare('status',$this->status);
		$criteria->compare('payment_status',$this->payment_status,true);
		$criteria->compare('jdata',$this->jdata,true);
		$criteria->compare('payment_method',$this->payment_method,true);
		$criteria->compare('payment_cat_id',$this->payment_cat_id);
		$criteria->compare('create_time',$this->create_time,true);
		$criteria->compare('ip',$this->ip,true);
		$criteria->compare('user_agent',$this->user_agent,true);
		
		$criteria->compare( 'order_time', $this->order_time, true);
		
		//add filter
		$criteria->compare( 'info_.checkout_date', $this->checkout_date, true);
		$criteria->compare( 'info_.business', $this->business);
		$criteria->compare( 'info_.invoice_type', $this->invoice_type);
		$criteria->compare( 'info_.invoice_status', $this->invoice_status);
		$criteria->compare( 'info_.contract_type', $this->contract_type);
		$criteria->compare( 'info_.invoice_export', $this->invoice_export);
		$criteria->compare( 'info_.spend_money', $this->spend_money);
		$criteria->compare( 'info_.receive_money', $this->receive_money);
		$criteria->compare( 'info_.tariff_plan', $this->tariff_plan);
		
		$text_search = trim($this->text_search);
		if(!empty($text_search))
		{
			$text_search = "'%".implode('%',explode(' ',trim($text_search)))."%'";
			$criteria->addCondition('(info_.website LIKE '.$text_search.') OR (user.website LIKE '.$text_search.') OR (user.fullname LIKE '.$text_search.') OR (user.code LIKE '.$text_search.')');
			
			
		}
		$criteria->together = true;
		$criteria->with = array(
			'info_',
			'user'
		);
		

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
			'pagination' => array(
				'pageSize' => Yii::app()->admin->getState('pageSize', Yii::app()->params['defaultPageSize']),
			),
			'sort'=>array(				
				'defaultOrder'=>'create_time ASC',
				
				'attributes' => array(
					'order_time',
					'code',
					'subtotal',
					'payment_method',
					'checkout_date'=>array(
						'asc'=>'info_.checkout_date',
						'desc'=>'info_.checkout_date DESC',
					),
				),
			),
		));
	}


3. and models/OrdersUser.php
<?php
class OrdersUser extends User 
{
	public function tableName()
	{
		preg_match("/dbname=(?P<dbname>[^;]*)/", $this->dbConnection->connectionString, $matches);
		return $matches['dbname'].'.{{user}}';
	}
}


4. and models/User.php
class User extends CommonAR
{
[b]public function getDbConnection()
	{
        return Yii::app()->db_sem;
    }
}[/b]


5. and gridview - protected/modules/admincp/views/orders/admin.php
<?php 

$this->widget('vH_CGridView', array(
	'id'=>'orders-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	
	'beforeAjaxUpdate' => 'js:function(data){
		$("select[name*=\"website\"] :selected").attr("selected",false);
	}',
	
	'addFilter' => array(
		//nhan vien kinh doanh
		array(
			'name' => 'business',
			'filter' => User::userStaff(false),
			'headerHtmlOptions' => array('width'=>90),
		),
		
		array(
			'name' => 'website',
			'headerHtmlOptions' => array('width'=>90),
			'filter' => Orders::getWebsiteFilter($model->business),
		),
		
		//loai hoa don
		array(
			'name' => 'invoice_type',
			'filter' => Category::getInvoiceTypes(false),
			'headerHtmlOptions' => array('width'=>90),
		),
		
		//trang thai hoa don
		array(
			'name' => 'invoice_status',
			'filter' => Category::getInvoiceStatus(false),
			'headerHtmlOptions' => array('width'=>120),
		),
		
		//loai hop dong dich vu
		array(
			'name' => 'contract_type',
			'filter' => Category::getContractTypes(false),
		),
		
		//hoa don - bien lai
		array(
			'name' => 'invoice_export',
			'filter' => Category::getInvoiceExportOptions(false),
			'headerHtmlOptions' => array('width'=>120),
		),
		
		//nguoi chi tien
		array(
			'name' => 'spend_money',
			'filter' => User::userStaff(false),
			'headerHtmlOptions' => array('width'=>120),
		),
		
		//nguoi nhan tien
		array(
			'name' => 'receive_money',
			'filter' => User::userStaff(false),
			'headerHtmlOptions' => array('width'=>120),
		),
		
		//goi dich vu
		array(
			'name' => 'tariff_plan',
			'filter' => Category::getTariffPlan(false),
			'headerHtmlOptions' => array('width'=>100),
		),
		
		//tim kiem text
		array(
			'name' => 'text_search',
		),
	),
	
	'columns'=>array(
		/*array(
			'header'=>'STT',
			'value'=>'$this->grid->dataProvider->pagination->currentPage * $this->grid->dataProvider->pagination->pageSize + ($row+1)',
			'htmlOptions'=>array('style'=>'text-align:center; width:30px; font-weight:bold'),
		),*/
		array(
			'class'=>'vH_CCheckBoxColumn',
		    'id'=>'ids', 
            'selectableRows'=>2, 
			'htmlOptions'=>array('style'=>'text-align:center; width:30px'),
		),
		//'order_id',
		array(
			'header' => '',
			'type' => 'raw',
			'value' => '!empty($data->info_->invoice_status_->picture_small) ? CHtml::image($data->info_->invoice_status_->getPictureSM(16)->src, "", array("title"=>$data->info_->invoice_status_->title_full)) : ""',
		),
		array(
			'name' => 'order_time',
			'type' => 'html',
			'filter' => CHtml::activeTextField($model, 'order_time', array('placeholder'=>'Kiểu nhập: 2013-12-31')),
			'headerHtmlOptions' => array('align'=>'left'),
			'value' => 'CHtml::link(date("Y-m-d - H:i", strtotime($data->order_time)), array("update","id"=>$data->order_id))',
		),
		array(
			'name' => 'code',
			'type' => 'html',
			'headerHtmlOptions' => array('align'=>'left'),
			'value' => 'CHtml::link($data->code, array("update","id"=>$data->order_id))',
		),
		array(
			'name' => 'checkout_date',
			'type' => 'raw',
			'filter' => CHtml::activeTextField($model, 'checkout_date', array('placeholder'=>'Kiểu nhập: 2013-12-31')),
			'value' => 'CHtml::link(date("Y-m-d", strtotime($data->info_->checkout_date)), array("update","id"=>$data->order_id))',
			'htmlOptions' => array('align'=>'center'),
		),
		array(
			'name' => 'order_info_text',
			'type' => 'raw',
			'filter' => false,
			'value' => '$data->gridUserAvatar . CHtml::link( $data->gridUserWebsite . vH_Utils::substr_(implode(", ", array_keys(CHtml::listData($data->products_title, "product_title","order_product_id"))),0,80), array("update","id"=>$data->order_id))',
			'headerHtmlOptions' => array('align'=>'left'),
		),
		array(
			'name' => 'amount',
			'type' => 'raw',
			'filter' => false,
			'value' => '$data->product_count',
			'htmlOptions' => array('align'=>'center'),
		),
		array(
			'name' => 'subtotal',
			'type' => 'raw',
			'filter' => false,
			'value' => 'CHtml::link( vH_Utils::priceFormat($data->subtotal)." VNĐ", array("update","id"=>$data->order_id))',
			'headerHtmlOptions' => array('align'=>'right'),
			'htmlOptions' => array('align'=>'right'),
		),
		array(
			'name' => 'payment_method',
			'filter' => false,
			'type' => 'raw',
			'value' => '!empty($data->payment_->picture_small) ? CHtml::link( CHtml::image($data->payment_->getPictureSM(16)->src), array("update","id"=>$data->order_id)) : ""',
			'htmlOptions' => array('align'=>'center'),
		),
		array(
			'name' => 'attach_count',
			'filter' => false,
			'type' => 'raw',
			'value' => '$data->attach_count',
			'htmlOptions' => array('align'=>'center'),
		),
		
		/*'user_type',
		'user_code',
		'user_id',
		
		'fullname',
		'address',
		'email',
		'phone',
		'province',
		'cmtnd',
		'csn_gender',
		'csn_info',
		'csn_fullname',
		'csn_address',
		'csn_email',
		'csn_phone',
		'csn_province',
		'csn_cmtnd',
		'csn_yahooid',
		'info',
		'status',
		'payment_status',
		'jdata',
		'payment_method',
		'payment_cat_id',
		'order_time',
		'create_time',
		'ip',
		'user_agent',
		*/
		
		array(
			'class'=>'EButtonColumnWithClearFilters',
			//'onClick_BeforeClear' => 'return true',
			'url'=>'Yii::app()->controller->createUrl(Yii::app()->controller->action->ID,array("clearFilters"=>1))',
			
			'template'=>'{update} {delete}',
			'htmlOptions'=>array('style'=>'width:40px; text-align:center'),
		),
	),
)); ?>

<?php
$css = <<<VH
.grid_user_avatar{
	position:relative;
	width:18px;
	height:18px;
	float: left;
	margin: 0 6px 0 0
}
.grid_user_avatar .lg{
	display:none;
	position:absolute;
	right:24px;
	top: 0
}
.grid_user_avatar:hover .lg{
	display:block;
}
VH;
Yii::app()->clientScript->registerCss('grid_user_avatar', vH_Utils::trimTotal($css) );
?>

Attached File(s)


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