Does CakePHP finderQuery work with SQL Server? Where would I debug that?

Question

I'm new to cakePHP, so I may be missing the obvious.

The system is running the latest download using Microsoft SQL Server 2005 as database. I appreciate that is slightly unusual, but having fixed the URL rewrite I have seen no other issues.

I'd like use a custom finderQuery, but I cannot even seem to replace the default. Specifically if I use

    var $hasMany = array(
        'RecyclateTypeConversion' => array(
        'className' => 'RecyclateTypeConversion',
        'foreignKey' => 'recyclate_type_id',
        'dependent' => false,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => 'select RecyclateTypeConversion.* from recyclate_type_conversions AS RecyclateTypeConversion WHERE RecyclateTypeConversion.recyclate_type_id IN ({$__cakeID__$});',
        'counterQuery' => ''
    ),
     };

I see this error

Notice (8): Undefined index: RecyclateTypeConversion [CORE\cake\libs\model\datasources\dbo_source.php, line 1099]

However the SQL debug output confirms that the query itself runs fine and returns 4 records, and the view runs perfectly when the finderQuery is not specified. I've tried for other hasMany tables too - with exactly the same issue.

I've attempted to replace the select all with specific field selects but I still see the same result. Certainly the query looks correct according to the manual - so what is the issue (and could it be related to using MSSQL?)

EDIT: Also, as this hasn't picked up any answers yet, what would be the best approach to debugging this? I've started hunting through the cake debugging class, but so far with no results that have enlightened me. Of course if there is a problem I'll be submitting the fix back to the project.

Answer 1

Have you checked that there is actually a model called RecyclateTypeConversion and that it exists with a filename according to the CakePHP conventions? I.e. is there a models/recyclate_type_conversion.php and in that file, is the name of the model defined as RecyclateTypeConversion.

The error that you're getting seems to hint that there's something wrong with that model name, as it cannot find the associated index.

Answer 2

Try removing the alias from the select - the casting in in the "AS RecyclateTypeConversion" part should handle that for you. I also like to wrap custom queries in double quotes. I might just be paranoid but string parsing errors have bit me in the ass before.

var $hasMany = array(
    'RecyclateTypeConversion' => array(
    'className' => 'RecyclateTypeConversion',
    'foreignKey' => 'recyclate_type_id',
    'dependent' => false,
    'finderQuery' => "select * from recyclate_type_conversions AS RecyclateTypeConversion WHERE RecyclateTypeConversion.recyclate_type_id IN ({$__cakeID__$});",
);

Also, I highly suggest you use the DebugKit plugin and post back to us the query log and a debug output of the find results that cause the errors.

Answer 3

did you go through it step by step?

1. try to get everything (all data)
2. try conditions
3. try "containable" behavior to create your query, which makes things a lot easier in my opinion