CakePHP joing two tables

Question

I have a problem in viewing my data ,I need to return a year,ex(2010,2011,2012,2013) instead of returning the year id , I used contain but It did not work , what is the right way to displaying data ?! Here is the output array:array(

(int) 0 => array(
        'IndicatorDatum' => array(
            'id' => '188',
            'indicator_id' => '2',
            'report_year_id' => '2',
            'value' => '144063181',
            'comment' => '0',
            'reference' => '0'
        )
    ),
    (int) 1 => array(
        'IndicatorDatum' => array(
            'id' => '163',
            'indicator_id' => '2',
            'report_year_id' => '3',
            'value' => '178104575',
            'comment' => '0',
            'reference' => '0'
        )
    ),
    (int) 2 => array(
        'IndicatorDatum' => array(
            'id' => '137',
            'indicator_id' => '2',
            'report_year_id' => '4',
            'value' => '198637602',
            'comment' => '0',
            'reference' => '0'
        )
    ),
    (int) 3 => array(
        'IndicatorDatum' => array(
            'id' => '5752',
            'indicator_id' => '2',
            'report_year_id' => '5',
            'value' => '1234',
            'comment' => null,
            'reference' => null
        )
    ),
    (int) 4 => array(
        'IndicatorDatum' => array(
            'id' => '5694',
            'indicator_id' => '2',
            'report_year_id' => '6',
            'value' => '100',
            'comment' => '0',
            'reference' => '0'
        )
    ),
    (int) 5 => array(
        'IndicatorDatum' => array(
            'id' => '5271',
            'indicator_id' => '2',
            'report_year_id' => '6',
            'value' => '1',
            'comment' => '0',
            'reference' => '0'
        )
    )
)

Model

    public $belongsTo = array(

        'Indicator' => array(
            'className' => 'Indicator',
            'foreignKey' => 'indicator_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'ReportYear' => array(
            'className' => 'ReportYear',
            'foreignKey' => 'report_year_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Organisation' => array(
            'className' => 'Organisation',
            'foreignKey' => 'organisation_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
    );

$this->find('all',array(
        'fields' => array('IndicatorDatum.id','IndicatorDatum.indicator_id','IndicatorDatum.report_year_id','IndicatorDatum.value','IndicatorDatum.comment','IndicatorDatum.reference'
        ),'contain' => array(
            'ReportYears'),

        'conditions'=>array('IndicatorDatum.indicator_id' =>'2' , 'IndicatorDatum.organisation_id'=>$organisation_id),
        'order' => array('IndicatorDatum.report_year_id')


));

Answer 1

You just need to specify the exact fields for each model(including associated models), otherwise only the id is being returned (and it is good, because if it would be all the fields, what if you have some text column in the db with KBs of data - you do not want to retrieve it)

also, please pay attention, that by default(and by cake conventions) model names are singular, so, inside contain it should be ReportYear and not ReportYears.

here is the final query.

    $this->find('all', array(
        'fields' => array(
            'IndicatorDatum.id',
            'IndicatorDatum.indicator_id',
            'IndicatorDatum.report_year_id',
            'IndicatorDatum.value',
            'IndicatorDatum.comment',
            'IndicatorDatum.reference' 
        ),
        'contain' => array(
            'ReportYear' => array(
                'fields' => array(
                    'ReportYear.id',
                    'ReportYear.year' // not sure what you have as a year field name
                ) 
            ) 
        ),
        'conditions' => array(
            'IndicatorDatum.indicator_id' => '2',
            'IndicatorDatum.organisation_id' => $organisation_id 
        ),
        'order' => array(
            'IndicatorDatum.report_year_id' 
        ) 
    )
    );