CF8 CFGRID not populating

[Dave Phipps]

Hi,

I am using a cfgrid to display a list of pending orders. It was all working until I modified the query to include some additional information.

Here is the query that works:

SELECT o.orderid,DATE_FORMAT(o.orderdate,'%d/%m/%Y') as odate,CAST(o.ordertotal AS CHAR) as ordertotal,c.firstname,c.lastname, (SELECT CASE pt.producttype WHEN 'POM' THEN 'Required' END AS IsPOM
FROM ordercontents oc, products p, producttype pt
WHERE oc.productid = p.productid
AND p.prodtypeid = pt.prodtypeid
AND o.orderid = oc.orderid
ORDER BY IsPOM DESC LIMIT 1) as IsPOM, (SELECT GROUP_CONCAT(prd.shortname ORDER BY prd.shortname SEPARATOR ' | ')
FROM products prd,ordercontents oco
WHERE oco.productid = prd.productid
AND o.orderid = oco.orderid) as ordereditems
FROM orders o, customer c
WHERE o.customerid = c.customerid
AND o.statusid = 1
ORDER BY o.orderdate DESC

and here is the query that works but doesn't populate the cfgrid:

SELECT o.orderid,DATE_FORMAT(o.orderdate,'%d/%m/%Y') as odate,CAST(o.ordertotal AS CHAR) as ordertotal,c.firstname,c.lastname, (SELECT CASE pt.producttype WHEN 'POM' THEN 'Required' END AS IsPOM
FROM ordercontents oc, products p, producttype pt
WHERE oc.productid = p.productid
AND p.prodtypeid = pt.prodtypeid
AND o.orderid = oc.orderid
ORDER BY IsPOM DESC
LIMIT 1) as IsPOM, (SELECT GROUP_CONCAT(CONCAT(prd.shortname,' x ',oco.quantity) ORDER BY prd.shortname SEPARATOR ' | ')
FROM products prd,ordercontents oco
WHERE oco.productid = prd.productid
AND o.orderid = oco.orderid) as ordereditems
FROM orders o, customer c
WHERE o.customerid = c.customerid
AND o.statusid = 1
ORDER BY o.orderdate DESC

This is the bit that seems to break the cfgrid:

GROUP_CONCAT(CONCAT(prd.shortname,' x ',oco.quantity) ORDER BY prd.shortname SEPARATOR ' | ')

whereas this works:

GROUP_CONCAT(prd.shortname ORDER BY prd.shortname SEPARATOR ' | ')

notice the addition of the ,' x ', oco.quantity in the query that doesn't work.

The text that this GROUP_CONCAT spits out is like this:

product1 x 2 | product2 x 42

If I run both versions of the query in the RDS query viewer I get results showing the correct information. The cfgrid however only populates from the query above.

The cfdebug shows no errors and shows that both queries return the same number of records. But the broken version does seem to get stuck at the ordereditems value which should be just a string (see above) from cfgrid's point of view.

By the way, I am using MySQL 5 with the built in MySQL driver for CF8 (hotfix 2).

Cheers,

Dave

[Dave Phipps · Accepted solution]

Just for info. I fixed it by using CAST to force it to a CHAR datatype:

CAST((SELECT GROUP_CONCAT(CONCAT(prd.shortname,' x ',oco.quantity) ORDER BY prd.shortname SEPARATOR ' | ')
FROM products prd,ordercontents oco
WHERE oco.productid = prd.productid
AND o.orderid = oco.orderid) as CHAR) as ordereditems

Cheers,

Dave