0

I want to use subquery with main query in cakephp 3. As I see this link, that is good to understand. But I want to use subquery in mysql FROM clause. i.e, "SELECT * FROM (SELECT * FROM table_name)". But I can't get any idea about syntax for it. If anyone have ever tried this in cakephp 3, then answer will be appreciated. Here are some code which I have tried but did't worked for me.

$purchases = TableRegistry::get('Purchases');
                $sublastitem = $purchases->find()
                    ->select([
                        'id',
                        'customer_id',
                        'item'
                    ])
                    ->where(function ($exp, $q) use($custids)
                    {
                        return $exp->in('customer_id', ['1','2']);
                    })
                    ->order(['id' => 'DESC']);

Above is the subquery which I want to use in FROM clause.

And these code I have tried (commented lines are tried. Others are as it is for all try).

// $lastitem = $sublastitem->find();
// $lastitem = $purchases->find($sublastitem);
// $lastitem = $purchases->all($sublastitem);
            $lastitem->select([
                'id',
            ]);
            $lastitem->group('customer_id');

I found a link which describes my question more clearly. But answer in this link is not acceptable for me as I want to execute ORDER BY before GROUP BY. Here is link

Here is query which I want in Cakephp 3.

SELECT * FROM (SELECT 'id', 'customer_id', 'item' FROM purchases WHERE customer_id IN (1, 2) ORDER BY id) t GROUP BY customer_id
Community
  • 1
  • 1
Akshay Vaghasiya
  • 1,597
  • 9
  • 36
  • 60
  • I would suggest to read the API docs, if you want to pass a subquery to the `FROM` part instead of the `SELECT` part as in the linked question, then you should have a look at `Query::from()`. – ndm Jan 28 '16 at 09:36
  • what is the sense of ordering by id? Since you are grouping you can't be sure of what id mysql will return. You could just use aggregate function like MIN(id). I'm not even sure you need a subquery. Take a look [here](https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html) – arilia Jan 28 '16 at 10:02
  • @ndm. You are right as I need something like this. But I don't get it that how to pass my subquery($sublastitem) in from as it seems like an object. – Akshay Vaghasiya Jan 28 '16 at 10:05
  • @arilia, There is full sense to do like this as I want to execute order by first and then after group by. In other word, I can say that I want last row of group of data without using join. – Akshay Vaghasiya Jan 28 '16 at 10:06
  • mysql manual says _the values chosen are indeterminate_ you are grouping without using aggregate functions. So mysq will choose randomly what value to show. Maybe you're lucky and get the wanted result, but you can't be sure – arilia Jan 28 '16 at 10:19
  • Do you want to list the purchases made by customers you've selected? (I.e. Show the purchases for customer 1 and 2?) That's what I _think_ you want to do? – Sevvlor Jan 28 '16 at 10:44

1 Answers1

1

You will have to create two separate query objects. That means that you will have to use a syntax similar to this:

$matchingUsers = $users->find('all')->select('id');

$matchingPaurchases = $purchases->find('all')->where(['user_id' => $matchingUsers]);

This will create something like this: SELECT purchase_id, purchase_price, purchase_item_id FROM purchases WHERE user_id IN(1,2,3,4,5)

See SubQueries in the CookBook

Sevvlor
  • 560
  • 1
  • 7
  • 24
  • As I updated my question. Please check it. Because this solution is not acceptable as I have problem in implementation of query, not logic. So I can't change this query as you suggested. – Akshay Vaghasiya Jan 28 '16 at 08:43
  • Is it possible to type out your query you want? Because frankly I don't really understand what you're asking for :/ – Sevvlor Jan 28 '16 at 08:50
  • I edited query. And you are right as I forgot to add order in subquery. – Akshay Vaghasiya Jan 28 '16 at 09:09