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?

`orders_archive` has a "belongs to" relationship to `orders`. So can't you just query all orders_archive from the current user and get the corresponding orders? — kero, Aug 13 '15 at 21:28
Thus it is possible to bring all the records in the archive, and how to get all the records, but not to be the ones that have been added to the archive? — entermix, Aug 14 '15 at 12:14

score 3 · Accepted Answer · answered Dec 03 '15 at 05:49

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();

score 0 · Answer 2 · answered Aug 14 '15 at 17:28

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.

How do subquery in ORM?

2 Answers2