cakedc search:when i use wildcards and and/or connectors i have this Error: Invalid argument supplied for foreach()

Question

i want search by competence forexample $data['competences'] =java+oracle,sql then the method of the request competence must know java is first oracle is second and sql is third. i have this Error Invalid argument supplied for foreach() i guess the prb is in $data = array('competences' =>'First%+Second%, Third%'); or at

$result = $this->CompetencesUser->parseCriteria($data);`

in my query request i have this surprise:

SELECT `User`.`id`, `User`.`username`, `User`.`nom`, `User`.`prenom`, `User`.`email`, `User`.`password`, `User`.`created`, `User`.`modified` FROM `dcsearch`.`users` AS `User` WHERE User.id in (SELECT `CompetencesUser`.`user_id` FROM `dcsearch`.`competences_users` AS `CompetencesUser` LEFT JOIN `dcsearch`.`competences` AS `Competence` ON (`CompetencesUser`.`competence_id` = `Competence`.`id`) WHERE ((((`Competence`.`competence` LIKE '%First\\%%') AND (`Competence`.`competence` LIKE '%Second\\%%'))) OR (`Competence`.`competence` LIKE '%Third\\%%'))) LIMIT 20

this is my model user.php

public $hasAndBelongsToMany = 

    array(
            'Competence' => array(
                'with' => 'CompetencesUser',
                'className' => 'Competence',
                'joinTable' => 'competences_users',
                'foreignKey' => 'user_id',

public $filterArgs = array(

    'competences' => array(

                'type' => 'subquery',
                'method' => 'findByCompetences',
                'field' => 'User.id',

             'connectorsAnd' => '+', 
             'connectorOr' => ',',
              'before' => true,
               'after' => false)

    );

 public function findByCompetences($data = array()) {



$this->CompetencesUser->Behaviors->attach('Containable', array(
            'autoFields' => false
        )
    );

    $this->CompetencesUser->Behaviors->attach('Search.Searchable');
    //$data = array('competences' => 'Cake');

    $data = array('competences' => 'First%+Second%, Third%');

    $result = $this->CompetencesUser->parseCriteria($data);

    $expected = array(0 => array('OR' => array(
        array('AND' => array(
            array('Competence.competence LIKE' => '%First\%%'),
            array('Competence.competence LIKE' => '%Second\%%'),
        )),
        array('AND' => array(
            array('Competence.competence LIKE' => '%Third\%%')
        )),
    )));


    $query = $this->CompetencesUser->getQuery('all', array(

       'conditions' =>array(
        $expected,
        $result),


        'fields' => array(
          'user_id'
       ),
        'contain' => array(
           'Competence'


   )));





     return $query;
}

Answer 1

I suspect that maybe your issue lies in either:

1. the filterArgs in the CompetencesUser model for the name competences, which you have not included... that is being processed to create the $result you have above.
2. the common process prg() component - which doesn't like it if $this->request->data is passed, but doesn't have the model alias as a nested data set... see this PR #200 on CakeDC/search

Here's a possible refactor start, with debugs to help you out:

<?php
/**
* Search subquery for CompetencesUser
*
* @param array $data input from user, eg: array('competences' => 'First%+Second%, Third%')
* @param array $config filterArgs config for this
* @return string $subquery SQL select to be used as a subquery
*/
public function findByCompetences($data = array(), $config = array()) {

  // just so you know your inputs and where... remove as needed
  $function = 'findByCompetences';
  debug(compact('function', 'data', 'config'));

  // Setup for subquery
  $this->CompetencesUser->Behaviors->attach(
    'Containable', 
    array('autoFields' => false)
  );
  $this->CompetencesUser->Behaviors->attach('Search.Searchable');

  $subqueryOptions = array(
    'fields' => array('user_id'),
    'contain' => array('Competence'),
    // these conditions are created by CompetencesUser->filterArgs
    //   want to customize what you send them?  manipulate or recreate $data
    // ?error here?  check filterArgs on CompetencesUser
    'conditions' => $this->CompetencesUser->parseCriteria($data),
  );

  // just so you know your the find options... remove as needed
  debug(compact('function', 'subqueryOptions'));

  $subquery = $this->CompetencesUser->getQuery('all', $subqueryOptions);

  // just so you know your output... remove as needed
  debug(compact('function', 'subquery'));

  return $subquery;
}