[FIXED] mysql FROM clause with subquery in Cakephp 3

Issue

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

Solution

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

Answered By - Sevvlor