PHP variable in ORM query

Need Help
1

Hi folks,
I am trying to find a way to use a PHP variable inside sql query in Controller function and I don’t seem to get it right. In my case it is a variable that defines a date and I would need to use it in WHERE clause in several Controller functions.
Variable is defined in AppControllers initialize function, since I have understood that it is the place to define global variables. Is it so?
I have defined the date and then set it, like this: $this->set(‘defined_date’, $defined_date);

So what I’m looking for is something like this:
query = $this->Model->find();
$results = $query
->select([‘model.date’])
->select([‘label’ => ‘model.column’])
->select([‘value’ => $query->func()->avg(‘model.column’)])
->group(‘model.column’)

            ->where(['model.date>' => '$defined_date])
            ->order(['value' => 'DESC']);

I get an error that $defined_date is not defined. How can I define it globally and is this the right way to use it in query?

E. I forgot to mention that $defined_date works in template so it is global, but how can I use it Controller?

E2. I just read about Components and created a Component that has a couple of functions and they return the dates I need. But the question remains: how do I use Component return values in query?

Many thanks! And sorry for this brain-smashing question flow…

2

Okay. There are plenty of questions to ask to clarify here. But this should help you to start.

The method call $this->set(‘defined_date’, $defined_date) will set the variable to the view context. After that, the new variable name (in this case, the same as your local variable name) is not really available in your local (controller in this case?) context.

You could do this:

//in a controller
$this->set(‘q_date’, $defined_date);

// then later in a controller
echo $this->_viewBuilder()->getVar('q_date'));

//or in a component 
echo $this->getController()-_viewBuilder()->getVar('q_date'));

But this doesn’t feel like a great solution.

Why not use a property of Controller to make the value generally available to that controller, its descendants and classes that hold a reference to the controller?

For example

// in AppController

public $q_date;

public function initialize() {
   parent::initialize();
   $this->q_date = time(); // whatever you're doing to set the date

   //also send the value to the View context
   $this->set('q_date', $this->q_date);
}

//in any controller that extends AppController
echo $this->q_date;

//in any Component running for a controller that extends AppController
echo $this->getController()->q_date;

//in any template or element
echo $q_date;

There are other alternatives too