1 Reply Latest reply on Jan 4, 2008 2:55 AM by Dave Phipps

    CF8 CFGRID not populating

    Dave Phipps Level 1
      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
        • 1. Re: CF8 CFGRID not populating
          Dave Phipps Level 1
          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