Adding a calculated field with data from another table

Question

So I have a model site(#id, name, start_date, end_date) which might be in pause(#id, site_id, start_date, end_date) and I want to keep track of pauses.

I started by adding an expander button in the site CRUD to be able to add/remove pauses. Works like a charm.

But now I would like to know directly in the site list the state of each. So I started by adding a new expression field and just test if the site is in progress or not:

$this->addExpression('state')->set('if(site.end_date IS NULL,"In Progress", if(now() < site.end_date, "In Progress", "Ended"))');

Works perfectly ! But now I can't think of the method to determine if the site is in pause or not and in fact I cannot reproduice the result above using addExpression()->set(function($m, $q) {});

Edit:

So the example given by DarkSide works like a charm, still I need now to mix the two result into an only field state:

$this->addExpression('state')->set(function($model, $select)
 {
   // Is it ended ?
   $ended = $select->expr('if(site.date_ended IS NULL,"In Progress", if(now() < site.date_ended, "In Progress", "Ended"))');

   // Is it in pause ?
   $paused = $model->refSQL('Site_Pause')
                   ->count()
                   ->where('date_started', '<', $select->expr('now()'))
                   ->where('date_ended', '>', $select->expr('now()'));

  if ($paused > 0)
    return 'paused';
  return $ended;
 });

As $model->refSQL() returns a DSQL Object I cannot use of if statements. Should I try to make a all in once SQL query or try like above to use if statements by getting the results from Dsql object ?

Answer 1

Ok got it ! I post here the solution if needed by anyone:

$this->addExpression('etat')->set(function($model, $select) { return ( $select->expr( 'IF ( site.date_ended IS NULL OR NOW() < site.date_ended, IF ( [f1] > 0, "Paused", "In Progress" ), "Ended" )' )->setCustom('f1', $model->refSQL('Site_Pause') ->count() ->where('date_started', '<', $select->expr('NOW()')) ->where('date_ended', '>', $select->expr('NOW()')) ) ); });