So I'm a bit stuck on the above and I keep getting an sql server error. Yes I'm using mssql server :| - not my database.
A little background, my relationships concerned look like this:
AssetMaintenanceRecord->(belongs to)->Asset->(belongs to)->Project->(has one)->ProjectManager
(note ProjectManager is like an alias model for a personnel table. The project table has an id for ProjectManager which is just the personnel id.
So I'm trying to do a simple filter whereby I select all the AssetMaintenanceRecords by a search on ProjectManager.
The related index function in AssetMaintenanceRecordsController.php looks like:
public function index() {
$conditions = NULL;
if (isset($this->params['url']['report']) && $this->params['url']['report'] == 'open') {
$conditions[] = array('CompletedDate' => NULL);
}
if (isset($this->params['url']['report']) && $this->params['url']['report'] == 'notified') {
$conditions[] = array('NotifiedDate BETWEEN ? AND ?' => array(date('M d Y g:iA', strtotime($this->params['url']['datefrom'])), date('M d Y g:iA', strtotime($this->params['url']['dateto']))));
}
if (isset($this->params['url']['report']) && $this->params['url']['report'] == 'completed') {
$conditions[] = array('CompletedDate BETWEEN ? AND ?' => array(date('M d Y g:iA', strtotime($this->params['url']['datefrom'])), date('M d Y g:iA', strtotime($this->params['url']['dateto']))));
}
if (isset($this->params['url']['project'])) {
$conditions[] = array('Asset.aCurrProject' => $this->params['url']['project']);
}
if (isset($this->params['url']['ptCode']) && $this->params['url']['ptCode'] != NULL) {
$conditions[] = array('Asset.ptCode' => $this->params['url']['ptCode']);
}
if (isset($this->params['url']['asset']) && $this->params['url']['asset'] != NULL) {
$conditions[] = array('Asset.aFullCode' => $this->params['url']['asset']);
}
if (isset($this->params['url']['pm']) && $this->params['url']['pm'] != NULL) {
$pm_search_terms = explode(' ', $this->params['url']['pm']);
foreach($pm_search_terms as $pm_search_term) {
$conditions[] = array(
'OR' => array(
'ProjectManager.PerGivenName LIKE' =>'%'.$pm_search_term.'%',
'ProjectManager.PerSurname LIKE' =>'%'.$pm_search_term.'%',
)
);
}
}
$this->paginate['AssetMaintenanceRecord'] = array(
'contain' => array(
'Asset' => array(
'Project' => array(
'ProjectManager'
))
),
'order' => 'CompletedDate ASC',
'limit' => 10
);
$planttype = $this->AssetMaintenanceRecord->Asset->PlantType->find('list');
$this->set(compact('planttype'));
$this->AssetMaintenanceRecord->recursive = -1;
$this->set('records', $this->paginate('AssetMaintenanceRecord', $conditions));
}
Without the ProjectManager filter it works fine and I can echo out the ProjectManager array etc. but when I enter in a search term I get this error:
The multi-part identifier "ProjectManager.PerSurname" could not be bound.
The executed sql looks like:
'SELECT TOP 10 [AssetMaintenanceRecord].[MtceRegID] AS [AssetMaintenanceRecord__0], [AssetMaintenanceRecord].[AssetID] AS [AssetMaintenanceRecord__1], CAST(CAST([AssetMaintenanceRecord].[MtceRegNote] AS VARCHAR(8000)) AS TEXT) AS [AssetMaintenanceRecord__2], [AssetMaintenanceRecord].[POno] AS [AssetMaintenanceRecord__3], CAST(CAST([AssetMaintenanceRecord].[NotifiedDate] AS VARCHAR(8000)) AS TEXT) AS [AssetMaintenanceRecord__4], CAST(CAST([AssetMaintenanceRecord].[CompletedDate] AS VARCHAR(8000)) AS TEXT) AS [AssetMaintenanceRecord__5], [AssetMaintenanceRecord].[MtceRegTitle] AS [AssetMaintenanceRecord__6], CAST(CAST([AssetMaintenanceRecord].[CreatedDate] AS VARCHAR(8000)) AS TEXT) AS [AssetMaintenanceRecord__7], [AssetMaintenanceRecord].[CreatedUserID] AS [AssetMaintenanceRecord__8], CAST(CAST([AssetMaintenanceRecord].[ModifiedDate] AS VARCHAR(8000)) AS TEXT) AS [AssetMaintenanceRecord__9], [AssetMaintenanceRecord].[ModifiedUserID] AS [AssetMaintenanceRecord__10], [Asset].[aID] AS [Asset__11], [Asset].[ptCode] AS [Asset__12], [Asset].[aNo] AS [Asset__13], [Asset].[aFullCode] AS [Asset__14], [Asset].[aDesc] AS [Asset__15], [Asset].[aMake] AS [Asset__16], [Asset].[aModel] AS [Asset__17], [Asset].[aSerialNo] AS [Asset__18], [Asset].[aRegNo] AS [Asset__19], CAST(CAST([Asset].[aRegExpDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__20], [Asset].[aActive] AS [Asset__21], [Asset].[aIncAssetRpt] AS [Asset__22], [Asset].[aIncFinanceRpt] AS [Asset__23], [Asset].[aIsTrailer] AS [Asset__24], [Asset].[aIsSurveyEquip] AS [Asset__25], [Asset].[aCostedItem] AS [Asset__26], [Asset].[aCostedPeriod] AS [Asset__27], [Asset].[aWarrantyPeriod] AS [Asset__28], [Asset].[aPONo] AS [Asset__29], CAST(CAST([Asset].[aPODate] AS VARCHAR(8000)) AS TEXT) AS [Asset__30], CAST(CAST([Asset].[aPOCostExGst] AS VARCHAR(8000)) AS TEXT) AS [Asset__31], [Asset].[aQtyStock] AS [Asset__32], [Asset].[aQtyInUse] AS [Asset__33], [Asset].[aCurrProject] AS [Asset__34], [Asset].[aCurrOperator] AS [Asset__35], [Asset].[aStolen] AS [Asset__36], CAST(CAST([Asset].[aStolenDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__37], [Asset].[aWO] AS [Asset__38], CAST(CAST([Asset].[aWODate] AS VARCHAR(8000)) AS TEXT) AS [Asset__39], [Asset].[aSold] AS [Asset__40], CAST(CAST([Asset].[aSoldDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__41], CAST(CAST([Asset].[aSoldPrice] AS VARCHAR(8000)) AS TEXT) AS [Asset__42], CAST(CAST([Asset].[aNotes] AS VARCHAR(8000)) AS TEXT) AS [Asset__43], CAST(CAST([Asset].[LastModDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__44], CAST(CAST([Asset].[CreatedDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__45], [Asset].[aCat] AS [Asset__46], [Asset].[aPoliceRptNo] AS [Asset__47], [Asset].[aRelatedAssetID] AS [Asset__48], [Asset].[aRelatedAssetFullCode] AS [Asset__49], [Asset].[aPayMethod] AS [Asset__50], [Asset].[aInvoiceNo] AS [Asset__51], CAST(CAST([Asset].[aLastFuelDate] AS VARCHAR(8000)) AS TEXT) AS [Asset__52], [Asset].[aFuelType] AS [Asset__53] FROM [tbMtceRegister] AS [AssetMaintenanceRecord] LEFT JOIN [tbAsset] AS [Asset] ON ([AssetMaintenanceRecord].[AssetID] = [Asset].[aID]) WHERE [CompletedDate] IS NULL AND [Asset].[aCurrProject] IS NULL AND (([ProjectManager].[PerGivenName] LIKE '%test%') OR ([ProjectManager].[PerSurname] LIKE '%test%')) ORDER BY [CompletedDate] ASC'
Looks ok to me but I'm obviously going wrong somwhere?
Any help appreciated.
Thanks,
-Brett
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
No comments:
Post a Comment