logo
down
shadow

Subqueries in CakePHP 3?


Subqueries in CakePHP 3?

Content Index :

Subqueries in CakePHP 3?
Tag : cakephp , By : KS9
Date : November 28 2020, 09:01 AM

I hope this helps you . to the link from user ndm_yesterday, I generated the following solution:
public function findImages(Query $query, array $options) {
    $this->hasMany('ProductCategoriesProducts');

    $subquery = $this->ProductCategoriesProducts->find('all')->select([
        'Products.image'
    ])->join([
        [
            'table'     => 'products',
            'alias'     => 'Products',
            'type'      => 'LEFT',
            'conditions'=> [
                'ProductCategoriesProducts.product_id = Products.id'
            ]
        ]
    ])->where([
        'ProductCategoriesProducts.product_category_id = ProductCategories.id'
    ])->order([
        'Products.turnover' => 'DESC'
    ])->limit(1);

    $query->select([
        'ProductCategories.id',
        'ProductCategories.name',
        'ProductCategories__image' => $subquery
    ])->where([
        'ProductCategories.name <>' => 'Unsorted'
    ])->order([
        'ProductCategories.name'    => 'asc'
    ]);

    return $query;
}

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

In SQL is it faster to use subqueries or to build the subqueries into tables and then query from those?


Tag : sql , By : Arun Thomas
Date : March 29 2020, 07:55 AM
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.

Not able to get data by subQueries (multiple subQueries with no direct relation)


Tag : mysql , By : Guyou
Date : March 29 2020, 07:55 AM
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

How to run subqueries in cakephp


Tag : php , By : Guid
Date : March 29 2020, 07:55 AM
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;

Join two subqueries and have a field: division of the results of two subqueries


Tag : mysql , By : Sharad
Date : March 29 2020, 07:55 AM
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

Subqueries in Cakephp 3.0


Tag : sql , By : Willem van Schevikho
Date : March 29 2020, 07:55 AM
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']);
Related Posts Related QUESTIONS :
shadow
Privacy Policy - Terms - Contact Us © scrbit.com