title: Virtual Field with dependency on other table/model
body: <p>A couple years ago I developed something with CakePHP 2.2. In my <code>Bill</code> model I have a <code>virtualField</code> which computes whether a given bill is “done” already:</p>
  
  <pre><code>scheduled_payment = 1
  &amp;&amp; (payed_beihilfe IS NOT NULL &amp;&amp; payed_beihilfe &gt; 0)
  &amp;&amp; (payed_pkv IS NOT NULL &amp;&amp; payed_pkv &gt; 0)
  </code></pre>
  
  <p>This has worked fine until there arose the situation that the <code>payed_beihilfe</code> is always zero as this part does not take any more role. The information about the distribution is in the field <code>Person.beihilfe_part</code> which ranges from 0 to 100. If it is one of the extremes, I do not want to consider the amount there. So I first tried this:</p>
  
  <pre><code>scheduled_payment = 1
  &amp;&amp; ((payed_beihilfe IS NOT NULL &amp;&amp; payed_beihilfe &gt; 0) || Person.beihilfe_part = 0)
  &amp;&amp; ((payed_pkv IS NOT NULL &amp;&amp; payed_pkv &gt; 0) || Person.beihilfe_part = 100)
  </code></pre>
  
  <p>This works when one looks at a single <code>Bill</code> object. However, as soon as one looks at a <code>Person</code>, I get the SQL errors that <a href="http://book.cakephp.org/2.0/en/models/virtual-fields.html#limitations-of-virtualfields" rel="nofollow">are mentioned in the manual</a>. Basically this says that one cannot have <code>virtualFields</code> that need a <code>JOIN</code> operation. This is a bummer as I really need to extend this “done” property that I have.</p>
  
  <p>This is my <code>Bill</code> class:</p>
  
  <pre><code>class Bill extends AppModel {
      public $displayField = 'amount';
  
      public $virtualFields = array(
              # XXX There is a limitation in the `virtualFields` that it cannot
              # use any sort of `JOIN`. Therefore I have to make due with the
              # data that is available in the `bills` table. This is a real
              # kludge but it seems to work.
              "done" =&gt;
                  "scheduled_payment = 1
                  &amp;&amp; ((payed_beihilfe IS NOT NULL &amp;&amp; payed_beihilfe &gt; 0) || (person_id = 7 &amp;&amp; date &gt; '2016-06-01'))
                  &amp;&amp; (payed_pkv IS NOT NULL &amp;&amp; payed_pkv &gt; 0)"
      );
  
      public $validate = array(
          'amount' =&gt; array(
              "rule" =&gt; array("comparison", "&gt;", 0),
              "message" =&gt; "Der Rechnungsbetrag muss größer als € 0 sein."
          )
      );
  
  
      public $belongsTo = array(
          'Doctor' =&gt; array(
              'className' =&gt; 'Doctor',
              'foreignKey' =&gt; 'doctor_id',
              'conditions' =&gt; '',
              'fields' =&gt; '',
              'order' =&gt; ''
          ),
          'Person' =&gt; array(
              'className' =&gt; 'Person',
              'foreignKey' =&gt; 'person_id',
              'conditions' =&gt; '',
              'fields' =&gt; '',
              'order' =&gt; ''
          )
      );
  }
  </code></pre>
  
  <p>This is the <code>Person</code> model:</p>
  
  <pre><code>class Person extends AppModel {
      public $displayField = 'name';
  
      public $hasMany = array(
          'Bill' =&gt; array(
              'className' =&gt; 'Bill',
              'foreignKey' =&gt; 'person_id',
              'dependent' =&gt; false,
              'conditions' =&gt; '',
              'fields' =&gt; '',
              'order' =&gt; '',
              'limit' =&gt; '',
              'offset' =&gt; '',
              'exclusive' =&gt; '',
              'finderQuery' =&gt; '',
              'counterQuery' =&gt; ''
          )
      );
  }
  </code></pre>
  
  <p>The querying happens for instance in the <code>PeopleController::view</code> function:</p>
  
  <pre><code>public function view($id = null) {
      $this-&gt;Person-&gt;id = $id;
      if (!$this-&gt;Person-&gt;exists()) {
          throw new NotFoundException(__('Invalid person'));
      }
      $this-&gt;set('person', $this-&gt;Person-&gt;read(null, $id));
  }
  </code></pre>
  
  <p>This will also draw in the related <code>Bill</code> objects and there it crashes as the <code>Person</code> table is not <code>JOIN</code>ed into the query that obtains the <code>Bill</code> objects related to the given <code>Person</code>. This happens implicitly within CakePHP, I just used the scaffolding.</p>
  
  <p>I have not worked with CakePHP in quite a while as I have moved away from web development mostly. In a very small Django project I saw that the rows from the SQL tables get instantiated into real Python objects. It would be really easy to add a function or field there. In CakePHP everything is just stored in associative arrays. Therefore I do not really see how to add a complex function that computes this “done” property.</p>
  
  <p>What is a sensible way to go about this?</p>