I have been building a module for Magento 2.2 but am having trouble figuring out the query. I have created one in PHPMyAdmin so I know it works there, but doing it with Model is escaping me.
The query is as follows:
SELECT * FROM (
SELECT customer_name,
customer_email,
entity_id
FROM `sales_order_grid`
WHERE status = 'complete'
) revrem
LEFT JOIN (
SELECT name, order_id
FROM sales_order_item
WHERE updated_at > '2017-11-28 00:00:01'
) revprod
ON revrem.entity_id = revprod.order_id