Hi All,
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
