How to convert sql statement into magentoquery in magento 2

Question

Hi Below are the 3 queries i need to convert it into magento queries..

1.

$details = $connection->fetchAll("SELECT sales_order.customer_id , sales_invoice.increment_id , sales_invoice.grand_total , sales_invoice.created_at , sales_order_payment.method FROM sales_order inner join sales_invoice on sales_invoice.order_id = sales_order.entity_id inner join sales_order_payment on sales_order_payment.parent_id = sales_order.entity_id WHERE sales_order.increment_id = '" .$increment_id. "'");

2.

$update_xml_generated = ("INSERT INTO hydrogen_xml_generation (increment_id,xml_generated) VALUES('" .$increment_id. "' , '1')");

3.

$connection->query($update_xml_generated);

Can any body help on this .I don't know how to do it in magento because am a fresher.Thanks in advance.

Answer 1

For your first query, As per Magento2 standard, you can inject Sales Collection Factory class,

protected $order;
public function __construct(
.....
\Magento\Sales\Model\OrderFactory $order,
.......
 ) {
 ...
 $this->order = $order;
 ........
 parent::__construct($context, $data);
}

Using this code you can get your order invoice and payment details

public function getInvoiceDetails($order_id){
 $orderdetails = $this->order->loadByIncrementId($increment_id);
 //below will give you payment details
 $payment = $orderdetails->getPayment();
 $orderdetails->getGrandTotal(); //you can get the grandtotal like this
 //below will get all invoices for the order
 foreach ($orderdetails->getInvoiceCollection() as $invoice)
 {
 $invoice_id = $invoice->getIncrementId();
 }
 }

For the second query where you are inserting data in your custom table please follow this answer to save data in custom table from standard Magento way