PHP ResultSetMappingBuilder::addFieldResult方法代码示例

 public function getTransactionsByMember(Corporation $corp, array $member_ids, Carbon $date)
 {
     $start = $date->copy();
     $start->subMonth()->setTime(0, 0, 0);
     $end = $date->copy();
     $end->setTime(23, 59, 59);
     $sql = 'SELECT jt.owner_id2, group_concat(DISTINCT jt.id) as ids
         FROM journal_transactions as jt
         LEFT JOIN accounts as acc on jt.account_id=acc.id
         WHERE  acc.corporation_id = :corp_id
         AND jt.owner_id2 in ( :owner_ids )
         AND jt.date >= :start_date
         AND jt.date <= :end_date
         GROUP BY jt.owner_id2';
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('AppBundle\\Entity\\JournalTransaction', 'jt');
     $rsm->addFieldResult('jt', 'owner_id2', 'owner_id2');
     $rsm->addFieldResult('jt', 'ids', 'id');
     $q = $this->getEntityManager()->createNativeQuery($sql, $rsm);
     $q->setParameter('corp_id', $corp->getId());
     $q->setParameter('owner_ids', $member_ids, Connection::PARAM_INT_ARRAY);
     $q->setParameter('start_date', $start);
     $q->setParameter('end_date', $end);
     $results = $q->getResult();
     $real_res = [];
     foreach ($results as $res) {
         $ids = explode(',', $res->getId());
         $rt = $this->createQueryBuilder('jt')->select('sum(jt.amount) as total_amount')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
         $r = $this->createQueryBuilder('jt')->select('jt')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
         $real_res[] = ['user' => $res->getOwnerId2(), 'total' => $rt, 'orig_ids' => $r];
     }
     return $real_res;
 }

 /**
  * Получаем последние диалоги пользователя
  *
  * @param int $id
  * @param int $count
  *
  * @return array
  */
 public function findByDialog($id, $count)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("UserMessagesBundle:Dialog", "d");
     $rsm->addFieldResult("d", "id", "id");
     $rsm->addFieldResult("d", "flags", "flags");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "du", "d", "usersTo");
     $rsm->addFieldResult("du", "users_to_id", "id");
     $rsm->addFieldResult("du", "realname", "realname");
     $rsm->addFieldResult("du", "path", "path");
     $rsm->addJoinedEntityResult("UserMessagesBundle:Messages", "m", "d", "messages");
     $rsm->addFieldResult("m", "message_id", "id");
     $rsm->addFieldResult("m", "text", "text");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "m", "users");
     $rsm->addFieldResult("u", "users_id", "id");
     $rsm->addFieldResult("u", "m_realname", "realname");
     $rsm->addFieldResult("u", "m_path", "path");
     $query = $this->getEntityManager()->createNativeQuery('
             SELECT 
               DISTINCT ON (d.id) d.id, 
               d.flags,
               du.id as users_to_id, 
               du.realname, 
               du.path,
               m.id as message_id, 
               m.text,
               u.id as users_id, 
               u.realname as m_realname, 
               u.path as m_path
             FROM dialog d
               LEFT JOIN users du ON d.users_to_id = du.id
               LEFT JOIN messages m ON d.id = m.dialog_id
               LEFT JOIN users u ON m.users_id = u.id
             WHERE d.users_id = ?
             GROUP BY d.id, d.flags, du.id, m.text, u.id, m.id
             LIMIT 20 OFFSET ?
         ', $rsm)->setParameters([1 => $id, 2 => $count]);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

    /**
     * Get all groups of a kind
     *
     * @param Kind $kind
     * @param integer|null $orgId
     * @return mixed
     */
    public function groupsOfKind(Kind $kind, $orgId = null)
    {
        if ($orgId) {
            $sql = <<<EOT
                    SELECT
                        p.id AS id,
                        p.lastName,
                        g.description,
                        g.avatar
                    FROM groups g
                    INNER JOIN parties p ON g.id = p.id
                    INNER JOIN p2p_relations rel ON p.id = rel.context
                    WHERE p.kind_id = ? AND rel.reference =?
                    ORDER BY p.id
EOT;
        } else {
            $sql = <<<EOT
                    SELECT
                        p.id AS id,
                        p.firstName,
                        p.lastName,
                        g.description,
                        g.avatar
                    FROM groups g
                    INNER JOIN parties p ON g.id = p.id
                    WHERE p.kind_id = ?
                    ORDER BY p.id
EOT;
        }
        $rsm = new ResultSetMappingBuilder($this->em);
        $rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Party', 'p');
        $rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Group', 'g');
        $rsm->addFieldResult('p', 'id', 'id');
        $rsm->addFieldResult('p', 'lastName', 'lastName');
        $rsm->addFieldResult('g', 'description', 'description');
        $qb = $this->em->createNativeQuery($sql, $rsm)->setParameter(1, $kind->id());
        if ($orgId) {
            $qb->setParameter(2, $orgId);
        }
        $groups = $qb->getScalarResult();
        return $groups;
    }

 /**
  * Find change_beneficiary signatures by client account id
  *
  * @param int $accountId
  * @return DocumentSignature[]
  */
 public function findChangeBeneficiaryByClientAccountId($accountId)
 {
     $sql = 'SELECT ds.* FROM document_signatures ds
             LEFT JOIN client_beneficiaries cb ON (ds.source_id = cb.id)
             WHERE cb.account_id = :account_id AND ds.active = :is_active AND ds.type = :type_change_beneficiary';
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addEntityResult('WealthbotSignatureBundle:DocumentSignature', 'ds');
     $rsm->addFieldResult('ds', 'id', 'id');
     $rsm->addFieldResult('ds', 'source_id', 'source_id');
     $rsm->addFieldResult('ds', 'document_id', 'document_id');
     $rsm->addFieldResult('ds', 'docusign_envelope_id', 'docusign_envelope_id');
     $rsm->addFieldResult('ds', 'type', 'type');
     $rsm->addFieldResult('ds', 'status', 'status');
     $rsm->addFieldResult('ds', 'active', 'active');
     $rsm->addFieldResult('ds', 'created', 'created');
     $rsm->addFieldResult('ds', 'updated', 'updated');
     $query = $this->_em->createNativeQuery($sql, $rsm);
     $query->setParameters(array('type_change_beneficiary' => DocumentSignature::TYPE_CHANGE_BENEFICIARY, 'account_id' => $accountId, 'is_active' => 1));
     return $query->getResult();
 }

 /**
  * Корзина пользователя
  *
  * @param int $id
  *
  * @return array
  */
 public function findByProductsUsersBasket($id)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("ShopOrderBundle:OrderItem", "oi");
     $rsm->addFieldResult("oi", "id", "id");
     $rsm->addFieldResult("oi", "number", "number");
     $rsm->addJoinedEntityResult("ShopOrderBundle:Order", "o", "oi", "order");
     $rsm->addJoinedEntityResult("ShopProductBundle:Product", "p", "oi", "product");
     $rsm->addFieldResult("p", "product", "id");
     $rsm->addFieldResult("p", "price", "price");
     $rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image");
     $rsm->addFieldResult("pi", "image_id", "id");
     $rsm->addFieldResult("pi", "path", "path");
     $query = $this->getEntityManager()->createNativeQuery('
             SELECT 
               DISTINCT ON (oi.id) oi.id,
               oi.product_id,
               oi.number,
               p.id as product,
               p.price,
               pi.id as image_id,
               pi.path
             FROM order_item oi
               LEFT JOIN "order" o ON oi.order_id = o.id
               LEFT JOIN product p ON oi.product_id = p.id
               LEFT JOIN product_image pi ON p.id = pi.product_id
             WHERE 
               o.users_id = ? 
               AND o.is_create_order = \'f\'
         ', $rsm)->setParameter(1, $id);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

 /**
  * return All Product Shop
  *
  * @param string $name
  * @param int $count
  *
  * @return array
  */
 public function findByProductShop($name, $count)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("ShopProductBundle:Product", "p");
     $rsm->addFieldResult("p", "id", "id");
     $rsm->addFieldResult("p", "price", "price");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "p", "likeProduct");
     $rsm->addFieldResult("u", "likes", "id");
     $rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image");
     $rsm->addFieldResult("pi", "image_id", "id");
     $rsm->addFieldResult("pi", "path", "path");
     $query = $this->getEntityManager()->createNativeQuery('
             SELECT DISTINCT ON (p.id) p.id, p.price, count(u.id) as likes, pi.id as image_id, pi.path
             FROM product p
               LEFT JOIN product_image pi ON pi.product_id = p.id
               LEFT JOIN product_like pl ON pl.product_id = p.id
               LEFT JOIN users u ON pl.users_id = u.id
               LEFT JOIN shops s ON s.id = p.shops_id
             WHERE s.unique_name = ?
             GROUP BY p.id, p.price, pi.id, pi.path
             LIMIT 16 OFFSET ?
         ', $rsm)->setParameters([1 => $name, 2 => $count]);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

 public function findLatestValuesForClientPortfolio($clientPortfolioId)
 {
     $sql = "SELECT cav.* FROM client_account_values cav\n          LEFT JOIN client_portfolio cp ON cav.client_portfolio_id = cp.id\n          WHERE cav.id = (SELECT id FROM client_account_values cav1\n                            WHERE cav1.system_client_account_id = cav.system_client_account_id\n                            ORDER BY cav1.date DESC\n                            LIMIT 1)\n          AND cav.client_portfolio_id = :clientPortfolioId";
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addEntityResult('WealthbotClientBundle:ClientAccountValue', 'cav');
     $rsm->addFieldResult('cav', 'id', 'id');
     $rsm->addFieldResult('cav', 'client_portfolio_id', 'client_portfolio_id');
     $rsm->addFieldResult('cav', 'system_client_account_id', 'system_client_account_id');
     $rsm->addFieldResult('cav', 'source', 'source');
     $rsm->addFieldResult('cav', 'total_value', 'total_value');
     $rsm->addFieldResult('cav', 'total_in_securities', 'total_in_securities');
     $rsm->addFieldResult('cav', 'total_cash_in_account', 'total_cash_in_account');
     $rsm->addFieldResult('cav', 'total_cash_in_money_market', 'total_cash_in_money_market');
     $rsm->addFieldResult('cav', 'date', 'date');
     $rsm->addFieldResult('cav', 'sas_cash', 'sas_cash');
     $rsm->addFieldResult('cav', 'cash_buffer', 'cash_buffer');
     $rsm->addFieldResult('cav', 'billing_cash', 'billing_cash');
     $rsm->addFieldResult('cav', 'required_cash', 'required_cash');
     $rsm->addFieldResult('cav', 'model_deviation', 'model_deviation');
     $rsm->addFieldResult('cav', 'investable_cash', 'investable_cash');
     $query = $this->_em->createNativeQuery($sql, $rsm);
     $query->setParameter('clientPortfolioId', $clientPortfolioId);
     return $query->getResult();
 }