Issue with pseudo fields when column is null

3 posts by 2 authors in: Forums > CMS Builder
Last Post: April 7, 2020   (RSS)

By tbcshifter - April 5, 2020

I wouldn't call this a bug, per se, but it's definitely an unnecessary performance issue that should be fixed.

  • You have a table (A) with a field (F) that is a List that is set to Get Options from Database (advanced) from table (B)
  • Field F is set to null in all records in the table.
  • You run getRecords() on table A with loadPseudoFields set to true.

In _getRecords_addPseudoFields() it will call getListOptionsFromSchema() with $selectedValues as an empty array.

This runs a query on table B with no WHERE clause, so it pulls the first 1000 rows from the table which can take a few seconds to run on a large table.

But there's no need to populate the label on a null value, so the whole thing doesn't even need to be run.

So what I've done is edit line 602+ as such:

elseif (!empty($selectedValues)) {
  $listOptions    = getListOptionsFromSchema($fieldSchema, null, false, $selectedValues);
  $values         = array_pluck($listOptions, '0');
  $labels         = array_pluck($listOptions, '1');
  $valuesToLabels = $listOptions ? array_combine($values, $labels) : [];
}
else {
  continue;
}

By tbcshifter - April 7, 2020 - edited: April 7, 2020

So it turns out there's a bug in that code sample that causes the :label pseudofield not to be populated on "options listed below" list type. This is my new code, but it may be more elegant to handle preventing this query directly in getListOptionsFromSchema().

elseif (@$fieldSchema['optionsType'] == 'table' && empty($selectedValues)) {
  $valuesToLabels = [];
}
else {
  $listOptions    = getListOptionsFromSchema($fieldSchema, null, false, $selectedValues);
  $values         = array_pluck($listOptions, '0');
  $labels         = array_pluck($listOptions, '1');
  $valuesToLabels = $listOptions ? array_combine($values, $labels) : [];
}

By daniel - April 7, 2020

Hi tbcshifter,

getListOptionsFromSchema() does have some cases where we do want to run the query without a WHERE condition, so I think your initial approach makes the most sense. I've added this to our build version for testing.

Thanks!

Daniel
Technical Lead
interactivetools.com