How to write inner join query in collection method (magento2)?

Question

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

Answer 1

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'");

Answer 2

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.