CODEWITHSUNDEEP
phpmysqlcakephp
Colonel Mustard
Colonel Mustard

Reputation: 1533

How to run subqueries in cakephp

Cakephp 2.6

I have a Model, Temps, which has many tickets. In the index view of Temps I want to return for each record, the ticket with the date closest to the current date.

In mySQL it can be done as 

'SELECT expiry_date FROM uploads WHERE expiry_date > CURDATE() ORDER BY expiry_date ASC LIMIT 1'

But I don't know how to run this as a sub query. My Current query to generate my results is as follows: (bearing in mind this has been configured for datatables) Tickets is an alias for the Upload Model

 public function getAjaxIndexData($data) {
            $tokens = explode(" ", $data['searchString']);
            $conditions = array(
                $this->alias . '.deleted' => false,
                'OR' => array(
                    'CONCAT(' . $this->alias . '.first_name," ",' . $this->alias . '.last_name) LIKE' => '%' . implode(' ', $tokens) . '%',
                ),
                $data['columnsFilter']
            );
            $fields = array(
                'id',
                'full_name',
                'pps_number',
                'mobile',
                'email',
                'start_date',
                'time_served'
            );
            $order = array(
                $data['orderField'] => $data['order'] 
            );
            $contain = array(
                'LocalOffice.name',
            );

            $options = array(
                'conditions' => $conditions,
                'fields' => $fields,
                'order' => $order,
                'contain' => $contain,
                'limit' => $data['limit'],
                'offset' => $data['start']
            );
            $optionsNoFields = array(
                'conditions' => $conditions,
                'contain' => $contain,
            );

            $result['draw'] = $data['draw']; 
            $result['recordsTotal'] = $recordTotal = $this->find('count');
            $result['recordsFiltered'] = $this->find('count', $optionsNoFields); 
            $result['data'] = $this->find('all', $options); //standard search
            $result['data'] = $this->formatTable($result['data']); 

            return json_encode($result);
        }

Within this query I would like to add a field that shows the nearest expiry date for each Temp.

How would I construct this?

Upvotes: 1

Views: 1070

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

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

Then adjust your fields array

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

Also, the query could be rewritten as ("temp" needs to be replaced with the model alias)

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

Which means that a potentially better performing query would be to move that subquery out of the columns of the SELECT statement to a JOIN. For example:

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;

Upvotes: 2

Related Questions