Subqueries in CakePHP 3?

will be helpful for those in need Making a blanket statement that using subqueries is a lot slower than other commands is just plain wrong. As usual, it depends. No one here is going to be able to give you a definitive answer to your question. But, this should be easy to figure out yourself. You can easily create both approaches, and examine the generated query plans in SSMS and the query statistics in Profiler (something you should be doing anyway). This will determine the best approach for your particular problem.

hop of those help? i want to get data from user_login table which contains following fields: , Use a LEFT JOIN. Like this:

select login.date, logout.date 
from 
    (
        select 
            userId,
            date 
        from 
            user_login where userId = 1 and status = 1 and date = now()
    ) 
        login
LEFT JOIN
    (
        select 
            date,
            userId 
        from 
            user_login 
        where
            status = 2 
            and date = now()
    ) logout
        ON logout.userId=login.userId

SELECT
    user_login.userId,
    MAX(CASE WHEN user_login.status=1 THEN date ELSE NULL END) AS loginDate,
    MAX(CASE WHEN user_login.status=2 THEN date ELSE NULL END) AS logoutDate,
FROM
    user_login
WHERE
    user_login.userId=1
    AND date = now()
GROUP BY
    user_login.userId

wish helps you Cakephp 2.6 , Dynamically create a virtual field:

$this->virtualFields['nearest'] = '(SELECT expiry_date FROM uploads WHERE expiry_date > CURDATE() AND uploads.owner_id = '.$this->alias.'.ticket_id  ORDER BY expiry_date ASC LIMIT 1')';

        $fields = array(
            'id',
            'full_name',
            'pps_number',
            'mobile',
            'email',
            'start_date',
            'time_served',
            'nearest'
        );

SELECT MIN(expiry_date)
FROM uploads 
WHERE expiry_date > CURDATE()
  AND uploads.owner_id = temp.ticket_id;

SELECT * 
FROM temp
LEFT JOIN (SELECT MIN(expiry_date) AS expiry,owner_id
    FROM uploads 
    WHERE expiry_date > CURDATE())
    GROUP BY owner_id) AS next_dates
  ON next_dates.owner_id = temp.ticket_id;

like below fixes the issue The simplest way to do it would be to change the select line:
SELECT a1.userid, a1.is_CWS, a2.Apply_num, a1.is_CWS/a2.Apply_num FROM (select userid,count(path) as is_CWS from TABLE where path='abc.com' group by userid having count(path)>1) a1 JOIN (select userid,count(userid) as Apply_num from TABLE where trackid is not NULL group by userid) a2 on a1.userid=a2.userid

this one helps. I have doubts that the query you are using is the best way to do what you want to achieve since it seems that the query and the subquery are returning the same value
Anyway this is how you can obtain the same exact query you asked

$q = $this->Articles->find();

$q->select([$q->func()->count('*')])
    ->where(['user_id' => 1]);

$q2 = $this->Users->find()
    ->select(['total_count' => $q])
    ->autoFields(true)
    ->where(['user_id' => 1])
    ->group(['user_id'])
    ->all();

$q = $this->Users->find()
    ->select(['total_count' => $q->func()->count('*')])
    ->autoFields(true)
    ->where(['user_id' => 1])
    ->group(['user_id']);