QueryGenerator

Wczytywanie filtru z modułu

Wczytanie filtru z konkretnego modułu

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

Dodawanie warunków

Dodanie do warunków wymaganych

$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]]);

dodawanie do warunków opcjonalnych

$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);

Dodanie join-ów

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');

Lista operatorów

Lista operatów pól dat

Pobranie danych

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

lub 

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

Sortowanie

$queryGenerator->setOrder('accountid');
$queryGenerator->setOrder('accountid','asc');
$queryGenerator->setOrder('accountid','desc');

Łączenie wyświetlanych danych 

$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)");

Dodanie kolumn nie będących polami

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

Dodanie pól

$queryGenerator->setField(['visibility', 'assigned_user_id', 'activitystatus']);

 Dodanie pól z modułu powiązanego

$queryGenerator->addReletedField([
 'sourceField' => 'xxxxxxxxxxx',
 'relatedModule' => 'OSSTimeControl',
 'relatedField' => 'timecontrol_type',
]);

Dodanie warunków z modułu powiązanego

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

Przykładowe zapytanie

SELECT
 `vtiger_account`.`accountid` AS `id`,
 `vtiger_osstimecontrolxxxxxxxxxxx`.`timecontrol_type` AS `OSSTimeControltimecontrol_type`
FROM
 `vtiger_account`
 INNER JOIN `vtiger_crmentity`
 ON vtiger_account.accountid = vtiger_crmentity.crmid
 LEFT JOIN `vtiger_osstimecontrol` AS `vtiger_osstimecontrolxxxxxxxxxxx`
 ON vtiger_account.xxxxxxxxxxx = vtiger_osstimecontrolxxxxxxxxxxx.timecontrol_type
WHERE (vtiger_crmentity.deleted = 0)
 AND (
 (
 (
 `vtiger_osstimecontrolxxxxxxxxxxx`.`timecontrol_type` = 'PLL_BREAK_TIME'
 )
 )
 )