ExpenseCount/app/Model/UserGroup.php

<?php
  

/**
 * @property Expense $Expense
 */
class UserGroup extends AppModel
{
    var $name='UserGroup';


    function list($fields = array('*'),$conditions = array() )
    {
        $returnData = array();
        $userGroups = $this->find('all', array(
            'fields' => $fields,
            'conditions' => $conditions,
        ));
        if(!empty($userGroups)){
            foreach ($userGroups as $userGroup){
                $returnData[] = $userGroup['UserGroup'];
            }
        }
        return $returnData;
    }

    function listWithExpense($fields = array('*'),$conditions = array() )
    {
        $returnData = array();
        $userGroups = $this->find('all', array(
            'fields' => $fields,
            'conditions' => $conditions,
            'joins' => array(
                array(
                    'table' => 'expenses',
                    'alias' => 'Expense',
                    'type' => 'INNER',
                    'conditions' => array(
                        'Expense.unique_url = UserGroup.unique_url'
                    )
                )
            )
        ));

        // TODO why loop
        if(!empty($userGroups)){
            foreach ($userGroups as $userGroup){
                $temp = array_merge($userGroup['UserGroup'], $userGroup['Expense']);
                $returnData[] = $temp;
            }
        }
        return $returnData;
    }

    public function groupListWithExpense($fields = array('*'), $conditions = array()) {
        $returnData = array();
        $startTime = microtime(true);

        $expenseType = isset($conditions['expense_type']) ? $conditions['expense_type'] : null;
        unset($conditions['expense_type']); // unset becausse user_groups has no column expense_type

        // Get all user groups first
        $userGroups = $this->find('all', array(
            'conditions' => $conditions
        ));

        if (empty($userGroups)) {
            return $returnData;
        }

        // Collect all unique_url values
        $uniqueUrls = Hash::extract($userGroups, '{n}.UserGroup.unique_url');
        $uniqueUrls = array_values(array_unique(array_filter($uniqueUrls)));

        if (empty($uniqueUrls)) {
            return $returnData;
        }

        $this->Expense = ClassRegistry::init('Expense');

        $expenseFields = array(
            'Expense.id',
            'Expense.unique_url',
            'Expense.title',
            'Expense.create_date'
        );

        // 1. Query expenses table once
        $expenseConditions = array('Expense.unique_url' => $uniqueUrls);
        if ($expenseType !== null) {
            $expenseConditions['Expense.expense_type'] = $expenseType;
        }
        $expenses = $this->Expense->find('all', array(
            'fields' => $expenseFields,
            'conditions' => $expenseConditions,
        ));

        $mainExpenses = Hash::extract($expenses, '{n}.Expense');
        $returnData = array_merge($returnData, $mainExpenses);

        // Find which unique_url values were not found
        $foundUrls = Hash::extract($expenses, '{n}.Expense.unique_url');
        $missingUrls = array_values(array_diff($uniqueUrls, $foundUrls));

        if (empty($missingUrls)) {
            return $returnData;
        }


        // 2. Query backup_expenses once
        /** @var DboSource $db */
        $db = ConnectionManager::getDataSource('default');

        $backupExpenses = $db->fetchAll(
            "SELECT id, unique_url, title, create_date
            FROM backup_expenses AS Expense
            WHERE unique_url IN (" . implode(',', array_fill(0, count($missingUrls), '?')) . ")",
            $missingUrls
        );

        $backupData = Hash::extract($backupExpenses, '{n}.Expense');
        $returnData = array_merge($returnData, $backupData);

        $foundBackupUrls = Hash::extract($backupExpenses, '{n}.Expense.unique_url');
        $missingUrls = array_values(array_diff($missingUrls, $foundBackupUrls));

        if (empty($missingUrls)) {
            return $returnData;
        }

         3. Query archive expenses once
        try {
            /** @var DboSource $archiveDb */
            $archiveDb = ConnectionManager::getDataSource('archieve'); // TODO

            $archiveExpenses = $archiveDb->fetchAll(
                "SELECT id, unique_url, title, create_date
                FROM expenses AS Expense
                WHERE unique_url IN (" . implode(',', array_fill(0, count($missingUrls), '?')) . ")",
                $missingUrls
            );

            $archiveData = Hash::extract($archiveExpenses, '{n}.Expense');
            $returnData = array_merge($returnData, $archiveData);
        } catch (MissingConnectionException $ex) {
            // Archive DB unavailable (e.g. local dev), skip silently
        }

        return $returnData;
    }

    function isExistUserGroup(int $user_id, string $unique_url)
    {
        $result = false;
        $userGroups = $this->find('first', array(
            'conditions' => array(
                'user_id' => $user_id,
                'unique_url' => $unique_url,
            ),
        ));
        if(!empty($userGroups)){
            $result = true;
            $userGroups = $userGroups['UserGroup'];
        }
        return array($result,$userGroups);
    }

    function add($inputData)
    {
        $result = false;

        if(!empty($inputData))
        {
            $fileds = array();
            $dbData["UserGroup"] = $inputData;
            if($this->save($dbData,false,$fileds)){
                $dbData["UserGroup"]['id'] = $this->id;
                $result = true;
            }
        }
        return array($result,$dbData["UserGroup"]);
    }

    function deleteUG($deleteConditions)
    {
        return $this->deleteAll(
            array($deleteConditions)
        );
    }

    function updateUG($updateFields,$updateConditions)
    {
        $result = false;
        if(!empty($updateFields) && !empty($updateConditions))
        {
            if($this->updateAll($updateFields, $updateConditions)){
                $result = true;
            }
        }
        return $result;
    }
}