How to execute a subquery in ORM?

Question

Kohana

There are tables:
orders:
id, author_id, consumer_id, supplier_id, manager_id, status_id, created
orders_archive
id, order_id, user_id
,
created which were created by the current user, and which were added / not added to the archive (by the same user)

$orders = ORM::factory('Order')
->where('author_id', '=', $this->user->id)
->and_where(?)

How to insert such a subquery? Do I understand correctly that DB::expr() should be used? How?

Answer 1

->where(null, 'EXISTS', DB::expr('(SELECT * FROM `order_archive` AS `oa` WHERE `oa`.`order_id` = `order`.`id` AND ...)'))

Answer 2

$orders = ORM::factory('Order')
->join('orders_archive', 'LEFT')->on('orders.id', '=', 'orders_archive.order_id')
->where('author_id', '=', $this->user->id)
->and_where(?)

Well, you need to specify what kind of ORM we are talking about!