We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Union query with Phalcon

Hello, how execute query with UNION in Phalcon+Mysql? ej:

SELECT SUM(TOTAL) FROM (SELECT GROUP_CD, LOCAL_CD, GUEST_NUM, SUM(IFNULL(CASH_PRECIO,0)+IFNULL(CHECK_PRECIO,0)+IFNULL(COUPON_PRICE,0)-IFNULL(TOTAL_PRECIO,0)) AS TOTAL FROM T_VENTA WHERE STAT_CD = '3200' AND GUEST_NUM = 10013196 AND GROUP_CD = 1049 AND LOCAL_CD = 1079 GROUP BY GROUP_CD, LOCAL_CD, GUEST_NUM UNION ALL SELECT GROUP_CD, LOCAL_CD, GUEST_NUM, SUM(IFNULL(CASH_PRECIO,0)+IFNULL(CHECK_PRECIO,0)+IFNULL(COUPON_PRICE,0)-IFNULL(TOTAL_PRECIO,0)) AS TOTAL FROM T_VENTA_OLD WHERE STAT_CD = '3200' AND GUEST_NUM = 10013196 AND GROUP_CD = 1049 AND LOCAL_CD = 1079 GROUP BY GROUP_CD, LOCAL_CD, GUEST_NUM UNION ALL SELECT GROUP_CD, LOCAL_CD, CLIENT_NUM, SUM(IFNULL(PRICE,0)) AS TOTAL FROM T_MNG_BANK WHERE STAT_CD = '1000' AND CHECK_FLAG = 'Y' AND CLIENT_NUM = 10013196 AND GROUP_CD = 1049 AND LOCAL_CD = 1079 GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM UNION ALL SELECT GROUP_CD, LOCAL_CD, CLIENT_NUM, SUM(IFNULL(TOTAL_PRECIO,0)) AS TOTAL FROM T_MNG_CREDIT WHERE STAT_CD = '1000' AND CLIENT_NUM = 10013196 AND GROUP_CD = 1049 AND LOCAL_CD = 1079 GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM UNION ALL SELECT GROUP_CD, LOCAL_CD, CLIENT_NUM, SUM(IFNULL(NOTE_PRICE,0)) AS TOTAL FROM T_MNG_NOTE WHERE STAT_CD = '1000' AND CLIENT_NUM = 10013196 AND GROUP_CD = 1049 AND LOCAL_CD = 1079 GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM ) TOTAL_CREDIT;

code 1: error $queryadmAuthSql = $this->modelsManager->createQuery($union); $resultadmAuthSql = $queryadmAuthSql->execute(); this return "Syntax error, unexpected token (, near to ...query"

code 2: error $query = new Query( $union, $this->getDI() ); $unionRow = $query->execute(); this return "Syntax error, unexpected token (, near to ...query"

how resolve this problem?



5.3k
Accepted
answer

Well I could solve it by looking in the documentation: I have to say that this query was taking 15 seconds on the msqyl command line, but with pdo flew !!! How can this be?

$db = \Phalcon\DI::getDefault()->get('db'); $stmt = $db->prepare($union); $stmt->execute(['guest_num' => $guest_num, 'u_group_cd' => $u_group_cd, 'u_local_cd' => $u_local_cd]); $oneResult = $stmt->fetch(); $row_credit_client = (isset($oneResult[0]) && !empty($oneResult[0])) ? $oneResult[0] : 0;



145.0k

Yea, you need to do this in plain sql. PHQL doesn't support union.