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

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'");
  • 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) – Hamza Mustafa Apr 14 '18 at 7:58
  • created_at column is present in both tables so its returning this ambiguous error using your query too bro – Hamza Mustafa Apr 14 '18 at 7:59
  • You need to modify group by too. Add main_table.created_at in group by clause. – Sohel Rana Apr 14 '18 at 8:06
  • how brother? please paster your query below i will mark as green – Hamza Mustafa Apr 14 '18 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'); – Hamza Mustafa Apr 14 '18 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, privacy policy and cookie policy

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