How do subquery in ORM?

Question

There are tables:

orders:

id
author_id
consumer_id
supplier_id
manager_id
status_id
created

orders_archive

id, 
order_id, 
user_id, 
created

That is, there are orders, any user can mark the order archive (customer, manager ..)

We need to get all the records that are created by the current user, and which are thus added to / have been added to the archive (the same user)

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

How to insert a sub-query? Do I understand correctly that you want to use DB::expr()? How?

Answer 1

I did it once not long ago where I used a sub-query in the where clause like so:

$query = ORM::factory('user')->from(array('users', 'user'));
$query->where(DB::Expr('NOT'), 'EXISTS', DB::Expr('(SELECT * FROM blacklists as b WHERE b.user_id = user.id AND b.target_user_id =' . $this->request->post('requester_id') . ')'));

but another way to do such a thing would be like this:

$query = DB::select()->from(array('table', 't'));
$sub = DB::select('imgname')->from('p_images')->where('pro_id','=',DB::Expr('`p`.`id`'))->and_where('imgtype','=','11')->limit(1);
$query->select(array($sub, 'prof_image'));
$p = $query->as_object()->execute();

Answer 2

As it's a different table, why not set up a new ORM Model for the orders_archive? Then something like the following code would do it:

$orders_archive = ORM::factory('Order_Archive')
->where('user_id', '=', $this->user->id)
->find_all();

Remove the ->and_where() completely.