分页上的SQL错误 [英] SQL error on pagination
问题描述
我们有一个完美的工作网页（index_admin）的Relationships控制器，但后添加分页它的所有崩溃。
Hey guys we have a perfectly working web page (index_admin) of the Relationships controller, but after adding pagination its all crashing.
正在浏览：
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Relationship.sender_id' in 'where clause'
客户和企业建立关系所以他们可以通过我们的网站交换发票。这里是数据库模式：
Customers and businesses build 'relationships' so they can exchange invoices over our website. Here is the DB schema:
id，sender_id，receiver_id，active，requested，expiry_date
id, sender_id, receiver_id, active, requested, expiry_date
Sender_id和receiver_id都是帐户表的外键。
Sender_id and receiver_id are both foreign keys to the Account Table. So in other words tells the db which accounts are linked to each other.
关系模型'BELONGSTO''接收者和发送者帐户模型'：
Relationship model 'BELONGSTO' 'RECEIVER AND SENDER ACCOUNT MODELS':
public $belongsTo = array(
'ReceiverAccount' =>array(
'className' => 'Account',
'foreignKey' =>'receiver_id',
'associationForeignKey' => 'accounts_id',
),
'SenderAccount' =>array(
'className' => 'Account',
'foreignKey' =>'sender_id',
'associationForeignKey' => 'accounts_id',)
);
Index_admin：
Index_admin:
public function index_admin(){
$this->set('title_for_layout', 'Relationships');
$this->set('stylesheet_used', 'homestyle');
$this->set('image_used', 'eBOXLogoHome.png');
$this->layout='home_layout';
//retrieve Account Id of current User
$accountid=$this->Auth->user('account_id');
//Conditions
$conditions=array(
"OR"=> array(
'Relationship.sender_id' => $accountid,
'Relationship.receiver_id' => $accountid)
);
//Find all Invoices where receiver_id = accountid
$relationships=$this->Relationship->find('all', array(
'conditions' => $conditions));
debug($conditions);
$compName = $this->Account->field('account_name', array('id' => 'Relationship.id'));
$this->paginate = array(
'limit' => 10,
'conditions'=> $conditions
);
$this->set('accountid', $accountid);
$this->set('relationship', $this->paginate());
$this->set('compName', $compName);
}
Index_admin视图（部分）：
Index_admin view (partial):
<table id="data">
<tr>
<td colspan=7 align='right'>
<?php
echo $this->Paginator->prev('<' . __('previous'), array(), null, array('class'=>'prev disabled'));
echo ' ';
echo $this->Paginator->numbers(array('seperator'=>''));
echo ' ';
echo $this->Paginator->next(__('next') . '>', array(), null, array('class'=>'next disabled'));
?>
</td>
</tr>
<tr>
<th><?php echo $this->Paginator->sort('id'); ?></th>
<th><?php echo $this->Paginator->sort('sender_id'); ?></th>
<th><?php echo $this->Paginator->sort('receiver_id'); ?></th>
<th><?php echo $this->Paginator->sort('expiry_date'); ?></th>
<th>Status</th>
<th>Actions</th>
</tr>
<?php foreach($relationship as $relationships):?>
<?php
if($relationships['Relationship']['requested']==1)
{
$status = 'Requested';
$bgcol = '#F8FAC0';
}
else if($relationships['Relationship']['active']==1)
{
$status = 'Active';
$bgcol = '#CFDAE8';
}
else if($relationships['Relationship']['active']==0)
{
$status = 'Expired';
$bgcol = '#FAB9B9';
}
if($relationships['Relationship']['active']==0 && $relationships['Relationship']['requested']==0)
{
$action = 'Reactivate';
}
else
{
$action = 'Edit Expiry';
}
if($relationships['Relationship']['sender_id']==$accountid)
{
$start = '<font color="#191970">';
$end = '</font>';
}
else
{
$start = NULL;
$end = NULL;
}
if($relationships['Relationship']['receiver_id']==$accountid)
{
$startr = '<font color="#191970">';
$endr = '</font>';
}
else
{
$startr = NULL;
$endr = NULL;
}
if($relationships['Relationship']['sender_id']==$accountid)
{
$acctname = $relationships['ReceiverAccount']['account_name'];
}
else if($relationships['Relationship']['receiver_id']==$accountid)
{
$acctname = $relationships['SenderAccount']['account_name'];
}
?>
<tr>
<td align='center'><?php echo $relationships['Relationship']['id']; ?></td>
<td align='center'><?php echo $start?><?php echo $relationships['SenderAccount']['account_name']; ?><?php echo $end ?></td>
<td align='center'><?php echo $startr?><?php echo $relationships['ReceiverAccount']['account_name']; ?><?php echo $endr ?></td>
<td align='center'><?php echo date('d.m.Y', strtotime($relationships['Relationship']['expiry_date'])); ?></td>
<td align='center' bgcolor='<?php echo $bgcol ?>'><?php echo $status ?></td>
<td align='center'>
<?php echo $this->Form->Html->link('Delete', array('controller' => 'Relationships','action'=>'delete',$relationships['Relationship']['id']), NULL, 'Are you sure you want to delete '. $acctname);
?> | <?php echo $action ?> </td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan=7 align='right'>
<?php
echo $this->Paginator->prev('<' . __('previous'), array(), null, array('class'=>'prev disabled'));
echo ' ';
echo $this->Paginator->numbers(array('seperator'=>''));
echo ' ';
echo $this->Paginator->next(__('next') . '>', array(), null, array('class'=>'next disabled'));
?>
</td>
</tr>
</table>
像我说的一切都在工作，现在不是，但我不能看看为什么。
Like I said it was all working before hand, now it isn't, but I can't see why.
推荐答案
设置调试模式总是明智的看到所有可能的错误。您刚刚分享了sql错误部分，从中可以清楚地看出目标表没有sender_id字段。我假设你已经调试模式。所以先看看生成的查询。
It is always wise to set the debug mode on to see all possible errors in detail. You've just shared the sql error part from which it is clear that the intended table doesn't have the "sender_id" field. I'm assuming you've debug mode on. So first have a look at the generated query. Then you'll find which table the query is trying to dig in.
如果您的查询引用了正确的表，您可以尝试：
If your query is referencing the correct table, you can try this:
public function index_admin(){
$this->set('title_for_layout', 'Relationships');
$this->set('stylesheet_used', 'homestyle');
$this->set('image_used', 'eBOXLogoHome.png');
$this->layout='home_layout';
//retrieve Account Id of current User
$accountid=$this->Auth->user('account_id');
//Conditions
$conditions=array(
"OR"=> array(
'Relationship.sender_id' => $accountid,
'Relationship.receiver_id' => $accountid)
);
App::import('Model', 'Relationship');
$objRelationship = new Relationship();
$this->paginate = array( "conditions" => $conditions, 'limit' => 10 );
$relationships = $this->paginate( $objRelationship );
$compName = $this->Account->field('account_name', array('id' => 'Relationship.id'));
$this->set('accountid', $accountid);
$this->set('relationship', $this->paginate());
$this->set('compName', $compName);
}
这篇关于分页上的SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
