分页的 SQL 错误 [英] SQL error on pagination
问题描述
嘿伙计们，我们有一个完美运行的关系控制器网页 (index_admin)，但在添加分页后，它全部崩溃了.
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、活动、请求、expiry_date
id, sender_id, receiver_id, active, requested, expiry_date
Sender_id 和receiver_id 都是Account Table 的外键.换句话说，就是告诉数据库哪些帐户是相互关联的.
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屋！
