How to join a table from other database?
protected/models/User.php ( db = db_sem )
4783
protected/models/Orders.php ( default db = shopphanmem )
4784
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!
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!
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:
public function relations()
{
return array(
'user' => array(self::BELONGS_TO, 'OrdersUser', 'user_id'),
'info_' => array(self::HAS_ONE, 'OrderInfoJoin', 'order_id'),
);
}
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',
),
),
),
));
}
<?php
class OrdersUser extends User
{
public function tableName()
{
preg_match("/dbname=(?P<dbname>[^;]*)/", $this->dbConnection->connectionString, $matches);
return $matches['dbname'].'.{{user}}';
}
}
class User extends CommonAR
{
[b]public function getDbConnection()
{
return Yii::app()->db_sem;
}
}[/b]
<?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) );
?>