I have been trying to figure out what is causing the exception when attempting to create a join for my Collections and group them with the main_table.

Here is the code from my Collection.php

<?php

class Companyname_CheckoutComments_Model_Resource_Comment_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract{


    protected function _construct() {

        $this->_init('checkoutcomments/comment');
    }
    public function addCustomerFilter($customer){

        $select = $this->getSelect();
        if ($customer->getId()){
            $select->where('order.customer_id?', $customer->getId());
        } else {
            //Force empty result set for not logged in customers
            $select->where('1=0');
        }

        $select->join(array('order' => $this->getTable('sales/order')),
                'order.entity_id = main_table.order_id',
                array('status', 'created_at'));

        $select->join(array('order_item' => $this->getTable('sales/order_item')),
                'order_item.order_id = main_table.order_id
                AND order_item.parent_item_id IS NULL ',
                array());

        /** Mage_Core_Model_Resource_Helper_Mysql4 */
        Mage::getResourceHelper('core')->addGroupConcatColumn($select, 'product_names', 'order_item.name', ', ' );
        $select->group('main_table.comment_id');
        return $this;
    }
}

The line that is giving me grief is:

$select->group('main_table.comment_id');

As it throws an error that I'm can't seem to fix

QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2') GROUP BY `main_table`.`comment_id`' at line 4

SELECT `main_table`.*, `order`.`status`, `order`.`created_at`, GROUP_CONCAT(order_item.name SEPARATOR ', ') AS `product_names` FROM `checkout_comments` AS `main_table` INNER JOIN `sales_flat_order` AS `order` ON order.entity_id = main_table.order_id INNER JOIN `sales_flat_order_item` AS `order_item` ON order_item.order_id = main_table.order_id AND order_item.parent_item_id IS NULL WHERE (order.customer_id'2') GROUP BY `main_table`.`comment_id`

If anyone knows the correct syntax to use please could you kindly advise or if there is some error in my code that I have missed.

Thanks in advance guys!

  • 1
    Have you checked the sql that is generated? Viewing the actual sql statement will give you a better idea of what is wrong. To get The sql simply echo $select (or die($select)) – ProxiBlue Nov 24 '13 at 15:28
  • @ProxiBlue Thanks for the tip See my edit^ – user1704524 Nov 24 '13 at 17:05
  • You should check for loggedin customer an just return null, instead of querying and check for where false! – Fabian Blechschmidt Nov 24 '13 at 21:09
up vote 1 down vote accepted

The problem was indeed line 4

$select->where('order.customer_id?', $customer->getId());

Has to be

$select->where('order.customer_id=?', $customer->getId());

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.