Skip to main content
Version: 7.x

QueryGenerator

The functionality is available for YetiForce version 3.5 and later

The QueryGenerator mechanism can be used to list any data from entity type modules.

Load filter from a specific module​

$queryGenerator = new \App\QueryGenerator('Accounts');
$queryGenerator->initForCustomViewById(106);

Add to mandatory conditions​

$queryGenerator->addCondition('accounttype', 'Customer', 'e');
$queryGenerator->addCondition('accounttype', 'Customer', 'e');
$queryGenerator->addCondition('id', 124, 'e');
$queryGenerator->addNativeCondition(['status' => 1, 'type' => 2]);
$queryGenerator->addNativeCondition(['id' => [4, 8, 15]]);

Add to optional conditions​

$queryGenerator->addCondition('accounttype', 'Customer', 'e',false);
$queryGenerator->addCondition('id', 124, 'e',false);
$queryGenerator->addNativeCondition(['status' => 1, 'type' => 2],false);
$queryGenerator->addNativeCondition(['id' => [4, 8, 15]],false);

Add joins​

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#join

$this->addJoin(['LEFT JOIN', 'vtiger_crmentity', 'vtiger_activity.activityid = vtiger_crmentity.crmid');

Operator list​

const STANDARD_OPERATORS

https://github.com/YetiForceCompany/YetiForceCRM/blob/developer/app/Condition.php#L65-L103

Date fields operators list​

const DATE_OPERATORS

https://github.com/YetiForceCompany/YetiForceCRM/blob/developer/app/Condition.php#L22-L58

Download data​

$rows = $queryGenerator->createQuery()->all();
foreach ($rows as &$row) {
}

or

$dataReader = $queryGenerator->createQuery()->createCommand()->query();
while ($row = $dataReader->read()) {
}

Sort​

$queryGenerator->setOrder('accountid');
$queryGenerator->setOrder('accountid', \App\Db::ASC);
$queryGenerator->setOrder('accountid', \App\Db::DESC);

Combine displayed data​

$queryGenerator->setConcatColumn('date_start', "CONCAT(vtiger_activity.date_start, ' ', vtiger_activity.time_start)");
$queryGenerator->setConcatColumn('due_date', "CONCAT(vtiger_activity.due_date, ' ', vtiger_activity.time_end)");

Add non-field columns​

$queryGenerator->setCustomColumn('u_yf_openstreetmap.lon');

Add fields​

$queryGenerator->setField(['visibility', 'assigned_user_id', 'activitystatus']);
$queryGenerator->addReletedField([
 'sourceField' => 'xxxxxxxxxxx',
 'relatedModule' => 'OSSTimeControl',
 'relatedField' => 'timecontrol_type',
]);
$queryGenerator->addReletedCondition([
 'sourceField' => 'xxxxxxxxxxx',
 'relatedModule' => 'OSSTimeControl',
 'relatedField' => 'timecontrol_type',
 'value' => 'PLL_BREAK_TIME',
 'operator' => 'e',
 'conditionGroup' => true,
]);