Magento Question posted in
The official documentation can be found here.

Inner Join and filter by date not working throws Integrity constraint violation error – Magento

I am trying to make an inner join on two tables to get data. and it worked however when I search to get date from the table. I get an error saying

Integrity constraint violation: 1052 Column 'created_at' in where clause 
is ambiguous, query was: SELECT COUNT(*) FROM `sales_flat_order` AS 
`main_table`
INNER JOIN `sales_flat_order_item` ON main_table.store_id = 
sales_flat_order_item.store_id WHERE (`created_at` >= '2017-04-01'

This is my code

$collection = Mage::getModel('sales/order')->getCollection();

$collection->addFieldToFilter('created_at');
$collection->getSelect()->join("sales_flat_order_item", 
"main_table.store_id = sales_flat_order_item.store_id", 
array('name','sku','order_id','quantity'));
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solved it. 

    $startDate = date("Y-m-d 00:00:00", strtotime( date( "Y-m-d 00:00:00", 
strtotime( date("Y-m-d 00:00:00") ) ) . "-10 months" ) );
$endDate = date("Y-m-d 00:00:00", strtotime( date( "Y-m-d 00:00:00", 
strtotime( date("Y-m-d 00:00:00") ) ) . "+1 day" ) );
$collection = Mage::getResourceModel('sales/order_item_collection')->addFieldToFilter('created_at', array('from' => $startDate, 'to' => $endDate)->addAttributeToSelect('*');
$this->setCollection($collection);
return parent::_prepareCollection();

  2. 0

    You can try something like this

    $collection = Mage::getModel('sales/order')->getCollection();
$collection->addFieldToFilter('main_table.created_at', '2017-04-01');
$collection->getSelect()->join("sales_flat_order_item",
    "main_table.store_id = sales_flat_order_item.store_id",
    array('name','sku','order_id','qty_ordered'));

    But your join looks weird maybe it would be better to use sales_flat_order.entity_id = sales_flat_order_item.order_id then store_id

    What exactly do you try to get by that select?

    Please, describe your task.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search