Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Union query with Phalcon

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

SELECT SUM(TOTAL) FROM (SELECT GROUPCD, LOCALCD, GUESTNUM, SUM(IFNULL(CASHPRECIO,0)+IFNULL(CHECKPRECIO,0)+IFNULL(COUPONPRICE,0)-IFNULL(TOTALPRECIO,0)) AS TOTAL FROM TVENTA WHERE STATCD = '3200' AND GUESTNUM = 10013196 AND GROUPCD = 1049 AND LOCALCD = 1079 GROUP BY GROUPCD, LOCALCD, GUESTNUM UNION ALL SELECT GROUPCD, LOCALCD, GUESTNUM, SUM(IFNULL(CASHPRECIO,0)+IFNULL(CHECKPRECIO,0)+IFNULL(COUPONPRICE,0)-IFNULL(TOTALPRECIO,0)) AS TOTAL FROM TVENTAOLD WHERE STATCD = '3200' AND GUESTNUM = 10013196 AND GROUPCD = 1049 AND LOCALCD = 1079 GROUP BY GROUPCD, LOCALCD, GUESTNUM UNION ALL SELECT GROUPCD, LOCALCD, CLIENTNUM, SUM(IFNULL(PRICE,0)) AS TOTAL FROM TMNGBANK WHERE STATCD = '1000' AND CHECKFLAG = 'Y' AND CLIENTNUM = 10013196 AND GROUPCD = 1049 AND LOCALCD = 1079 GROUP BY GROUPCD, LOCALCD, CLIENTNUM UNION ALL SELECT GROUPCD, LOCALCD, CLIENTNUM, SUM(IFNULL(TOTALPRECIO,0)) AS TOTAL FROM TMNGCREDIT WHERE STATCD = '1000' AND CLIENTNUM = 10013196 AND GROUPCD = 1049 AND LOCALCD = 1079 GROUP BY GROUPCD, LOCALCD, CLIENTNUM UNION ALL SELECT GROUPCD, LOCALCD, CLIENTNUM, SUM(IFNULL(NOTEPRICE,0)) AS TOTAL FROM TMNGNOTE WHERE STATCD = '1000' AND CLIENTNUM = 10013196 AND GROUPCD = 1049 AND LOCALCD = 1079 GROUP BY GROUPCD, LOCALCD, CLIENTNUM ) 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?



615
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(['guestnum' => $guestnum, 'ugroupcd' => $ugroupcd, 'ulocalcd' => $ulocalcd]); $oneResult = $stmt->fetch(); $rowcreditclient = (isset($oneResult[0]) && !empty($oneResult[0])) ? $oneResult[0] : 0;

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