CakePHP : Joining table with max value

0
votes

Sorry im new in cakePHP. i use cakePHP 2.2. I have two table, Books and Transactions.

Table : Books

id
title
author

Table : Transactions

id
book_id
status
borrow_date

In table transaction list all transaction of the book.

My question, how to get status of max transaction id?

I try use this

options['joins'] = array(
            array(
                'table' => 'transactions',
                'alias' => 'Transaction',
                'type' => 'RIGHT OUTER',
                'fields' => array('MAX(Transaction.id)', '*'),
                'conditions' => array(
                    'Catalogue.id = Transaction.catalogue_id',
                    //'Transaction.user_id' => $userId
                ),
                'order' => array('Transaction.id' => 'desc'),
            )
        );
        $this->set('Book', $this->Book->find('all',$options));

at view:

$Book['Transaction']['status'];

But it shows an error:

Notice (8): Undefined index: Transaction [APP\View\Catalogues\user_katalog.ctp, line 32]

2
cakephpjoincakephp-2.2

2 Answers

0
votes

You are doing find(all) so you have a list of books

var_dump($Book);

you will see

array(
   0 => array(
       'Transaction' => ...
   ),
   1 => array(
       'Transaction' => ...
   )
   .....
);

not

array(
   'Transaction' => ...
);

Either loop through $Book or use find('first', ...) if you want one.

0
votes

dogmatic69 is right. Here's an example: since you've used this in controller:

$this->set('Book', $this->Book->find('all',$options));

you should use this in view:

foreach( $Book as $b ) {
      echo $b['Transaction']['status'];
}