Cakephp hasMany with multiple foreign keys that are not primary keys

Question

I have to use an existing database that can't be modified. There are two tables I need to link with a hasMany relationship (SGRPART hasMany SSGART).

The difficulty is that these tables are joined with two columns and none are primary keys:

Structure of table SGRPART:
IDSGRAPART (int 9)
CGRPART (int 9)
CSOUSGRP (int 9)
DESGRPART (varchar 100)

Structure of table SSGART:
IDSSGART (int 9)
CGRPART (int 9)
CSOUSGRP (int 9) DESGRPART (varchar 100)

The relationship should be: SGRPART.CGRPART = SSGART.CGRPART AND SGRPART.CSOUSGRP = SSGART.CSOUSGRP

I tried this in the SGRPART model but it doesn't return the associated records from the table SSGART:

class SGRPART extends AppModel
{
public $name = 'SGRPART';
public $useTable = 'SGRPART';
public $primaryKey = 'IDSGRPART';
public $displayField = 'DESGRPART';

var $hasMany = array(
'SSGART' => array(
'foreignKey' => false,
'conditions' => array('SSGART.CGRPART' => 'SGRPART .CGRPART','SSGART.CSOUSGRP'=>'SGRPART .CSOUSGRP')
)
);

}

Any idea if it's feasible in Cakephp and if yes how to do it?

Thanks, James

Hi, Small suggestion why Your using hasmany ? hasone is easy to use — Karthik Keyan
I agree but there are more than one record in the SSGART table for each record in table SGRPART. — Whatsnew
Well, foreignKey has to be set to null otherwise the association will be made on the primary key which is not relevant here. — Whatsnew
I suspect you will have to use a custom finder for this. I am still trying to figure this out myself. — Lars Ebert

Karthik Keyan Karthik Keyan · Accepted Answer · 2015-06-09T15:28:00

Could You pls try this

public $hasMany = array(
    'SSGART' => array(
        'className' => 'SSGART',
        'foreignKey' => 'IDSGRPART',
        'dependent' => true,
        'conditions' => array('SSGART.CGRPART' => 'SGRPART .CGRPART','SSGART.CSOUSGRP'=>'SGRPART .CSOUSGRP')
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => '',
        'counterQuery' => ''
    ),
);

put it in "SGRPART" model

Cakephp hasMany with multiple foreign keys that are not primary keys

1 Answers