2

My Two Table Image is given below,

enter image description here

I have a relation in TblB.php Model

public $belongsTo = array('TblA');

I use MySQL databse;

$results = $this->TblB->find('all', array('group' => array('TblB.tbl_a_id')));

Actually, I need last group of data in the table (tbl_b).
How to solve the query using CakePHP.

MD. ABDUL Halim
  • 712
  • 3
  • 11
  • 32

1 Answers1

0

You want to run the following SQL query to grab the data you want:-

SELECT TblB.* FROM tbl_b AS TblB WHERE TblB.id = (
    SELECT TblB2.id FROM tbl_b AS TblB2 
    WHERE TblB2.tbl_a_id = TblB.tbl_a_id 
    ORDER BY TblB2.id DESC 
    LIMIT 1
)
ORDER BY TblB.id ASC

See the sqlfiddle for this in action.

To do this in CakePHP you will need to build the sub-query using Cake's buildStatement:-

$db = $this->TblB->getDataSource();
$subQuery = $db->buildStatement(
    array(
        'fields' => array('"TblB2"."id"'),
        'table' => $db->fullTableName($this->TblB),
        'alias' => 'TblB2',
        'limit' => 1,
        'offset' => null,
        'joins' => array(),
        'conditions' => array('"TblB"."tbl_a_id" = "TblB2"."tbl_a_id"'),
        'order' => '"TblB2"."id" DESC',
        'group' => null

    )
);
$subQueryExpression = $db->expression($subQuery);

$conditions[] = $subQueryExpression;
$data = $this->TblB->find('all', compact('conditions'));

See the docs for more on retrieving data using a sub-query.

drmonkeyninja
  • 8,490
  • 4
  • 31
  • 59
  • I don't think your sql is correct. If your subquery only returns 1 row, then only 1 id will be chosen. The sample data shows that 3 different id's are needed in the solution. – AgRizzo Jun 24 '15 at 11:49
  • @AgRizzo I've test this query against the sample data shown in the question and it gave the desired result. I've seen this method used elsewhere, although can't remember the source. – drmonkeyninja Jun 24 '15 at 11:53
  • @AgRizzo http://stackoverflow.com/a/924501/311992 (I believe this is a similar situation just not in CakePHP). – drmonkeyninja Jun 24 '15 at 11:55
  • I started a [sqlfiddle](http://sqlfiddle.com/#!9/a6f30/1) with your code. At a minimum, your SQL has a couple syntax errors. – AgRizzo Jun 24 '15 at 12:42
  • @AgRizzo sorry, there was a typo in my original query. I've fixed that. As the question relates to CakePHP there should really be a primary key on `tbl_b` which is what I've based my answer on; if not then my answer should at least provide some guidance to solving the question being asked. See the updated SQL fiddle: http://sqlfiddle.com/#!9/0ca3e/1/0 – drmonkeyninja Jun 24 '15 at 13:05
  • Sorry, I misread your query, thinking the subquery was a JOIN table, not in the WHERE. FYI - The subquery in the WHERE clause may not perform as well as a subquery as a JOIN table. I built another [sqlfiddle](http://sqlfiddle.com/#!9/0fc4a/1) with many more rows and to show another solution. – AgRizzo Jun 24 '15 at 15:25