Query with conditions on a virtual field is not possible: how to trick it

Need Help
1

Hello,

I am using a $filter that is supposed to add a $condition on a virtual field in my find $query.
I understood that virtual fields can’t be used in a $query, so how can trick it, without adding a new field in database?

Here is what I wrote:
public function index($lessorId = null) {
if (null != ($this->request->getParam(‘lessor_id’))) {
$conditions = [‘lessor_id’ => $this->request->getParam(‘lessor_id’)];
if (null != ($this->request->getParam(‘natural_person_id’))) {
//un natural_person_id est passé
$condtions[] = [
‘natural_person_id’ => $this->request->getParam(‘natural_person_id’)];
} else {
// pas de natural_person_id de passé
$filter = $this->request->getQuery(‘filter’);
if (isset($filter) && is_string($filter)) {
switch ($filter) {
case ‘holder’:
$filter = [
‘is_holder’ => true];
break;

							default:
								//Par défaut, rien
								break;
						}					
				}				
			}
			//Traitement des filtres passé

			$query = $this->NaturalPeople->find();
			$query
				->where($conditions)
				->select(['id', 'reference', 'gender', 'firstname', 'name', 'phone', 'mobile', 'email', 'style'])
				->order(['name' => 'ASC'])
				->contain(['Addresses' => function($q) {
					return $q
					->where(['type IN' => ['1']]);
					}]);	

	        /* Affichage d'un message d'information si aucun contact n'existe pour le moment */
	        if ($query->count()) {
	            $jsonResponse['success']    =   true;   
	        } else {
	        	$tuto = DS. 'templates' . DS . 'tutos' . DS . 'tuto06.html' ; 
	            $jsonResponse['tuto'] = $tuto;                       
	        }
			$npd = $query->toArray();


	        $jsonResponse['message']   =   '';   
	        $jsonResponse['data']      =   $npd;   
			$this->set(compact('jsonResponse'));

		} else { // index de tous les baux de toutes les bailleurs de l'utilisateur connecté
			// cette fonction n'est pas disponible pour l'instant
		}
	}

Thank you

2

Check this: https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause?answertab=votes#tab-top

They surrounded the main query with another one, and it works…

1 Like