[2.7] Find condition "IN" array

[xv1t]

Not predictable behavior in the query.

Look at $document_types variable. If

$document_types = array(1);
$this->Document->find('all', array(
  'conditions' => array(
      'Document.document_type_id' => $document_types
)
));

then SQL query staitment:

WHERE `Document`.`document_type_id` = (1)

And wrongly belongsTo reference has been;

My workaround method

But in the array $document_types more then one element,

$document_types = array(1, -1); // -1 - is not existing record

SQL

WHERE `Document`.`document_type_id` IN (1, -1)

And belongsTo to references are correctly constructs.

it s bug?

Best Regards!

[ADmad]

How does it reference the wrong association? While the query is different logically field = (1) is the same as field IN (1).

[xv1t]

Ok. In details.

It is quite interchangeable SQL operations, but the result array is wrong.

class Document extends AppModel {
    public $actsAs = array('Tree');

    var $belongsTo = array(
        'DocumentRegistryType' => array(
            'foreignKey' => 'document_type_id'
        )
    );

    var $hasOne = array(
        'VisaDocument',
        'ExecuteDocument',
        'FirstDocument'
    );
}

In this query was two elements.

$conditions = array(
'DocumentRegistryType.document_registry_id' => array(3,1)
);

And SQL query is right too:

SELECT * FROM `iodoc`.`documents` AS `Document` LEFT JOIN `iodoc`.`document_registry_types` AS `DocumentRegistryType` ON (`Document`.`document_type_id` = `DocumentRegistryType`.`id`) LEFT JOIN `iodoc`.`visa_documents` AS `VisaDocument` ON (`VisaDocument`.`document_id` = `Document`.`id`) LEFT JOIN `iodoc`.`execute_documents` AS `ExecuteDocument` ON (`ExecuteDocument`.`document_id` = `Document`.`id`) LEFT JOIN `iodoc`.`first_documents` AS `FirstDocument` ON (`FirstDocument`.`document_id` = `Document`.`id`) 
WHERE 
`DocumentRegistryType`.`document_registry_id` IN (3, 1) -- Right use IN operator with list
ORDER BY `Document`.`date` DESC LIMIT 10

And we have a Right document array;

$write_document = array(
            'Document' => array(
                'id' => '91',
                'name' => '78634765',
                'date' => '2015-12-09',
                'document_type_id' => '7',
                'parent_id' => null,
                'lft' => '65',
                'rght' => '66',
                'created' => '2015-12-08 21:07:12',
                'updated' => '2015-12-10 15:54:50',
                'create_user_id' => null,
                'update_user_id' => null,
                'test' => false
            ),
            'DocumentRegistryType' => array(
                'id' => '7',
                'document_registry_id' => '1',
                'name' => 'Информация',
                'created' => '2015-12-08 21:27:42',
                'updated' => '2015-12-13 18:04:49',
                'create_user_id' => null,
                'registry' => 'Входящие', // <--- Remember it
                'icon' => 'fa fa-sign-in', // <---- And remember that
                'update_user_id' => null,
                'can_in_appeal_chain' => true,
                'overlay_resolution' => true,
                'set_violation_of_rights' => false,
                'set_restore_of_rights' => true,
                'confirm_violation_of_rights' => false,
                'document_type_id' => '7'
            ),
            'VisaDocument' => array(
                'document_id' => null,
                'visa' => null,
                'comment' => null,
                'created' => null,
                'updated' => null,
                'create_user_id' => null,
                'update_user_id' => null
            ),
            'ExecuteDocument' => array(
                'document_id' => null,
                'executed' => null,
                'comment' => null,
                'created' => null,
                'updated' => null,
                'create_user_id' => null,
                'update_user_id' => null
            ),
            'FirstDocument' => array(
                'document_id' => '91',
                'first_document' => '91',
                'lead' => '1'
            )
        ),

if the

$conditions = array(
'DocumentRegistryType.document_registry_id' => array(1)
);

It s wrong object array!

array(
            'Document' => array(
                'id' => '91',
                'name' => '78634765',
                'date' => '2015-12-09',
                'document_type_id' => '7',
                'parent_id' => null,
                'lft' => '65',
                'rght' => '66',
                'created' => '2015-12-08 21:07:12',
                'updated' => '2015-12-10 15:54:50',
                'create_user_id' => null,
                'update_user_id' => null,
                'test' => false
            ),
            'DocumentRegistryType' => array(
                'id' => '7',
                'document_registry_id' => '1',
                'name' => 'Информация',
                'created' => '2015-12-08 21:27:42',
                'updated' => '2015-12-13 18:04:49',
                'create_user_id' => null,
                'update_user_id' => null,
                'can_in_appeal_chain' => true,
                'overlay_resolution' => true,
                'set_violation_of_rights' => false,
                'set_restore_of_rights' => true,
                'confirm_violation_of_rights' => false,
                'document_type_id' => '7'
            ),
            'document_registries' => array( // <----What it this???????
                'registry' => 'Входящие',
                'icon' => 'fa fa-sign-in'
            ),
            'VisaDocument' => array(
                'document_id' => null,
                'visa' => null,
                'comment' => null,
                'created' => null,
                'updated' => null,
                'create_user_id' => null,
                'update_user_id' => null
            ),
            'ExecuteDocument' => array(
                'document_id' => null,
                'executed' => null,
                'comment' => null,
                'created' => null,
                'updated' => null,
                'create_user_id' => null,
                'update_user_id' => null
            ),
            'FirstDocument' => array(
                'document_id' => '91',
                'first_document' => '91',
                'lead' => '1'
            )
        );

SQL statitment

SELECT * FROM `iodoc`.`documents` AS `Document` LEFT JOIN `iodoc`.`document_registry_types` AS `DocumentRegistryType` ON (`Document`.`document_type_id` = `DocumentRegistryType`.`id`) LEFT JOIN `iodoc`.`visa_documents` AS `VisaDocument` ON (`VisaDocument`.`document_id` = `Document`.`id`) LEFT JOIN `iodoc`.`execute_documents` AS `ExecuteDocument` ON (`ExecuteDocument`.`document_id` = `Document`.`id`) LEFT JOIN `iodoc`.`first_documents` AS `FirstDocument` ON (`FirstDocument`.`document_id` = `Document`.`id`) 
WHERE `DocumentRegistryType`.`document_registry_id` = (1) -- That place is not IN
ORDER BY `Document`.`date` DESC LIMIT 10

[ADmad]

The difference in results array is indeed weird though I am not sure it's due to difference in query. But I don't have any others ideas as to the cause either. Hopefully someone else does.

Do either of your Document or DocumentRegistryType models or attached behaviors have an afterFind() ?

[xv1t]

Im not use a afterFind() in the models.
And attaching this method for the correct this wrong result - not a good idea.

Easier, is a put int the array ( of conditions list) more then one elements, and result is right structure.

[ADmad]

And attaching this method for the correct this wrong result - not a good idea.

I wasn't suggesting using afterFind() to fix the issue. I asked in cause that was the cause.

[xv1t]

I can't determine the cause, so I write this issue

[markstory]

You can and probably should include an explicit IN when doing your find:

$this->Document->find('all', array(
  'conditions' => array(
      'Document.document_type_id IN' => array(1)
)
));

Will generate the correct queries all the time. The type inference around IN clauses has always been a bit odd which was why it was removed in 3.x

[markstory]

My last comment was complete lies, disregard it.

[markstory]

This was fixed in 2.7.10. See #8125