0

How to write this query in magento2 collection method?

SELECT o.increment_id , h.status, h.created_at
FROM sales_order AS o
INNER JOIN sales_order_status_history AS h ON h.parent_id = o.entity_id
WHERE o.entity_id = 530 AND h.status = 'processing_closed'; 

I have this function filter records/reports of sales, I want to change condition and defined above query here in collection method :

public function applyBaseFilters($collection)
    {
        $collection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns([
                'period' =>'DAYNAME(created_at)',
                'total_orders' => 'COUNT(entity_id)',
                'total_items' => 'SUM(total_item_count)',
                'subtotal' => 'SUM(base_subtotal)',
                'tax' => 'SUM(base_tax_amount)',
                'status' => 'status',
                'shipping' => 'SUM(base_shipping_amount)',
                'discounts' => 'SUM(base_discount_amount)',
                'total' => 'SUM(base_grand_total)',
                'invoiced' => 'SUM(base_total_invoiced)',
                'refunded' => 'SUM(base_total_refunded)',
                'entity_id' => 'CONCAT(entity_id,\''.$this->createUniqueEntity().'\')'
            ])
        ;

         $collection->addFieldToFilter('status', 'processing_closed');
    }

Please guide I am a beginner, thanks.


edit

I think I am so closely with the query but now I am facing this error on the screen using this query.

$collection->getSelect()->join(
    'sales_order_status_history',
// note this join clause!
    'main_table.entity_id = sales_order_status_history.parent_id',
    array('created_at')
)
->where("sales_order_status_history.status = 'processing_closed'");

Error : SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in group statement is ambiguous, query was: SELECT main_table.*, sales_order_status_history.created_at FROM sales_order AS main_table INNER JOIN sales_order_status_history ON main_table.entity_id = sales_order_status_history.parent_id WHERE (sales_order_status_history.status = 'processing_closed') AND (main_table.created_at >= '2018-04-05 19:00:00') AND (main_table.created_at <= '2018-04-13 18:59:59') GROUP BY DAYNAME(created_at) Exception #1 (PDOException): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in group statement is ambiguous

0

2 Answers 2

0

Change following way:


$collection->getSelect()->join(
    'sales_order_status_history',
// note this join clause!
    'main_table.entity_id = sales_order_status_history.parent_id',
    array('sales_order_status_history.created_at')
)
->where("sales_order_status_history.status = 'processing_closed'");
6
  • Integrity constraint violation: 1052 Column 'created_at' in group statement is ambiguous, query was: SELECT main_table.*, sales_order_status_history.created_at FROM sales_order AS main_table INNER JOIN sales_order_status_history ON main_table.entity_id = sales_order_status_history.parent_id WHERE (sales_order_status_history.status = 'processing_closed') AND (main_table.created_at >= '2018-04-06 19:00:00') AND (main_table.created_at <= '2018-04-14 18:59:59') GROUP BY DAYNAME(created_at) Apr 14, 2018 at 7:58
  • created_at column is present in both tables so its returning this ambiguous error using your query too bro Apr 14, 2018 at 7:59
  • You need to modify group by too. Add main_table.created_at in group by clause.
    – Sohel Rana
    Apr 14, 2018 at 8:06
  • how brother? please paster your query below i will mark as green Apr 14, 2018 at 8:34
  • $collection->getSelect()->join( 'sales_order_status_history', // note this join clause! 'main_table.entity_id = sales_order_status_history.parent_id', array('sales_order_status_history.created_at') ) ->where("sales_order_status_history.status = 'processing_closed'"); $collection->getSelect()->group('main_table.created_at'); Apr 14, 2018 at 8:42
0

There are generally two scenarios when this type of error occurs.

1) At column level

Whenever any DML commands(update or delete) are fired violating any check constraints on a column then such an error(SQLSTATE[23000]) occurs.

Make sure that the DML commands are not violating any check constraints.

2) At table level

SQLSTATE[23000] error occurs whenever any duplicate row is inserted in a table which have an unique constraint or unique index.

Check for the data that is being inserted. The data should be unique if the table has any unique constraint or unique index.

So check for the above two scenarios and make the necessary changes according to your requirement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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