Defining relationship between no primary key fields

Supposing I have two tables…




people

id int unsigned not null auto_increment primary key,

username varchar(30),

password varchar(30)


comms

id int unsigned not null auto_increment primary key,

telephone varchar(20),

email varchar(130),

username varchar(30)



So, whith my user model I want to define a comms relationship where the join is people.username=comms.username. How do I do that? All efforts so far have failed.

I think I need to explain myself a bit clearer…

Here’s my two tables;




[b]person[/b]

id int unsigned not null auto_increment primary key,

person_reference char(10),

username varchar(30),

password varchar(30),

//..


[b]agreements[/b]

id int unsigned not null auto_increment primary key,

person_reference char(10),

tstamp int unsigned not null

//..



Now I select my user using their person_reference




$user = person::model()->find('person_ref=:ref', array(':ref' => 'FKIE40ASL9'));



Now I want to select their agreements that are joined by person_reference




$user->agreements



A person may have many agreements, so I figure the relationship to be…




public function relations()

{

   return array(

    'agreements' => array(self::HAS_MANY, 'person', 'person_ref')

   );

}



But this is bringing back zero results and I know that it should be bringing back at least one. I think it has someting to do with me trying to join objects on columns that are not their primary key, but I am lost trying to find a solution.

try defining the ‘on’ ans the alias too:




public function relations()

{

   return array(

       'agreements' => array(self::HAS_MANY, 'agreements', 'person_ref','on'=>'t.person_reference = agreements.person_reference','alias'=>'agreements')

  );

}



Get an sql error now…




Error in querying SQL: SELECT [tenagree].[tag_ref] AS [t1_c0],

[tenagree].[prop_ref] AS [t1_c1], [tenagree].[tenancy_service] AS [t1_c2],

[tenagree].[tenancy_other] AS [t1_c3], [tenagree].[cur_bal] AS [t1_c4],

[tenagree].[cur_nr_bal] AS [t1_c5], [tenagree].[additional_debit] AS

[t1_c6], [tenagree].[occ_status] AS [t1_c7], [tenagree].[master_tag] AS

[t1_c8], [tenagree].[prdno_on_vac] AS [t1_c9], [tenagree].[year_on_vac] AS

[t1_c10], [tenagree].[occ_phase] AS [t1_c11], [tenagree].[hb_expire] AS

[t1_c12], [tenagree].[ass_date] AS [t1_c13], [tenagree].[fd_charge] AS

[t1_c14], [tenagree].[hb_freq] AS [t1_c15], [tenagree].[reason_term] AS

[t1_c16], [tenagree].[receiptcard] AS [t1_c17], [tenagree].[recgrossorder]

AS [t1_c18], [tenagree].[lastgrosscol] AS [t1_c19], [tenagree].[lastreccol]

AS [t1_c20], [tenagree].[lastrecline] AS [t1_c21], [tenagree].[cardbal] AS

[t1_c22], [tenagree].[recstatus] AS [t1_c23], [tenagree].[curcardno] AS

[t1_c24], [tenagree].[recgrosdate] AS [t1_c25], [tenagree].[cur_action_set]

AS [t1_c26], [tenagree].[cur_action_no] AS [t1_c27],

[tenagree].[tag_action] AS [t1_c28], [tenagree].[agr_type] AS [t1_c29],

[tenagree].[rech_tag_ref] AS [t1_c30], [tenagree].[master_tag_ref] AS

[t1_c31], [tenagree].[sup_ref] AS [t1_c32], [tenagree].[tstamp] AS

[t1_c33], [tenagree].[nosp] AS [t1_c34], [tenagree].[ntq] AS [t1_c35],

[tenagree].[eviction] AS [t1_c36], [tenagree].[committee] AS [t1_c37],

[tenagree].[suppossorder] AS [t1_c38], [tenagree].[possorder] AS [t1_c39],

[tenagree].[courtapp] AS [t1_c40], [tenagree].[nospexpire] AS [t1_c41],

[tenagree].[courtdate] AS [t1_c42], [tenagree].[ntqexpire] AS [t1_c43],

[tenagree].[visitdate] AS [t1_c44], [tenagree].[tenure_ori] AS [t1_c45],

[tenagree].[occ_phase_ori] AS [t1_c46], [tenagree].[open_item] AS [t1_c47],

[tenagree].[allocation_method] AS [t1_c48], [tenagree].[man_scheme] AS

[t1_c49], [tenagree].[anal_method] AS [t1_c50], [tenagree].[inv_type] AS

[t1_c51], [tenagree].[con_key] AS [t1_c52], [tenagree].[major_phase] AS

[t1_c53], [tenagree].[forwardaddress] AS [t1_c54], [tenagree].[acc_type] AS

[t1_c55], [tenagree].[tenagree_sid] AS [t1_c56], [tenagree].[noticegiven]

AS [t1_c57], [tenagree].[potentialenddate] AS [t1_c58],

[tenagree].[rtb_date] AS [t1_c59], [tenagree].[rtb_issued_by] AS [t1_c60],

[tenagree].[rtb_year] AS [t1_c61], [tenagree].[rtb_work] AS [t1_c62],

[tenagree].[rtb_amount] AS [t1_c63], [tenagree].[rtb_project] AS [t1_c64],

[tenagree].[rtb_recharge] AS [t1_c65], [tenagree].[rtb_budget] AS [t1_c66],

[tenagree].[last_action_date] AS [t1_c67], [tenagree].[last_action] AS

[t1_c68], [tenagree].[high_action_date] AS [t1_c69],

[tenagree].[high_action] AS [t1_c70], [tenagree].[last_balance] AS

[t1_c71], [tenagree].[tag_action_date] AS [t1_c72],

[tenagree].[ent_act_status] AS [t1_c73], [tenagree].[monitoring] AS

[t1_c74], [tenagree].[monit_date] AS [t1_c75], [tenagree].[monit_prd_type]

AS [t1_c76], [tenagree].[next_monit_date] AS [t1_c77],

[tenagree].[process_group_id] AS [t1_c78], [tenagree].[arrears_case] AS

[t1_c79], [tenagree].[cur_araction_sid] AS [t1_c80], [tenagree].[pmandata]

AS [t1_c81], [tenagree].[cur_action_subno] AS [t1_c82],

[tenagree].[collect_cash] AS [t1_c83], [tenagree].[evictdate] AS [t1_c84],

[tenagree].[lettertext] AS [t1_c85], [tenagree].[w2propactiondate] AS

[t1_c86], [tenagree].[rtb_effective] AS [t1_c87], [tenagree].[rtb_term] AS

[t1_c88], [tenagree].[s125_issued] AS [t1_c89], [tenagree].[u_oldtenref] AS

[t1_c90], [tenagree].[core_ver] AS [t1_c91], [tenagree].[u_paymentmethod]

AS [t1_c92], [tenagree].[u_ddpayref] AS [t1_c93], [tenagree].[u_s13rent] AS

[t1_c94], [tenagree].[u_starter_legal] AS [t1_c95],

[tenagree].[u_relet_rsn] AS [t1_c96], [tenagree].[u_sp] AS [t1_c97],

[tenagree].[u_possorder_date] AS [t1_c98], [tenagree].[u_possorder_bal] AS

[t1_c99], [tenagree].[u_evict_bal] AS [t1_c100], [tenagree].[u_grant_poss]

AS [t1_c101], [tenagree].[u_grant_poss_date] AS [t1_c102],

[tenagree].[u_hb_delay] AS [t1_c103], [tenagree].[u_abandon] AS [t1_c104],

[tenagree].[comp_avail] AS [t1_c105], [tenagree].[comp_display] AS

[t1_c106], [tenagree].[revdatann] AS [t1_c107], [tenagree].[phased] AS

[t1_c108], [tenagree].[ten_b_forward] AS [t1_c109], [tenagree].[vm_propref]

AS [t1_c110], [tenagree].[noticegiven_dt] AS [t1_c111],

[tenagree].[keysrecd_dt] AS [t1_c112], [tenagree].[u_homebuy_serv_ch] AS

[t1_c113], [tenagree].[u_cpr_visit_date] AS [t1_c114],

[tenagree].[u_hb_clear] AS [t1_c115], [tenagree].[u_agree_maint] AS

[t1_c116], [tenagree].[u_agree_place] AS [t1_c117],

[tenagree].[u_month_pay] AS [t1_c118], [tenagree].[u_evict_warr] AS

[t1_c119], [tenagree].[u_warr_date] AS [t1_c120], [tenagree].[u_warr_bal]

AS [t1_c121], [tenagree].[u_warr_outcome] AS [t1_c122],

[tenagree].[house_ref] AS [t1_c123], [tenagree].[tag_desc] AS [t1_c124],

[tenagree].[prd_sno] AS [t1_c125], [tenagree].[cot] AS [t1_c126],

[tenagree].[eot] AS [t1_c127], [tenagree].[tenure] AS [t1_c128],

[tenagree].[prd_code] AS [t1_c129], [tenagree].[spec_terms] AS [t1_c130],

[tenagree].[other_accounts] AS [t1_c131], [tenagree].[active] AS [t1_c132],

[tenagree].[present] AS [t1_c133], [tenagree].[terminated] AS [t1_c134],

[tenagree].[free_active] AS [t1_c135], [tenagree].[nop] AS [t1_c136],

[tenagree].[ra_date] AS [t1_c137], [tenagree].[rentgrp_ref] AS [t1_c138],

[tenagree].[succession_date] AS [t1_c139], [tenagree].[ori_rent] AS

[t1_c140], [tenagree].[ori_service] AS [t1_c141], [tenagree].[rent] AS

[t1_c142], [tenagree].[service] AS [t1_c143], [tenagree].[other_charge] AS

[t1_c144], [tenagree].[differential] AS [t1_c145],

[tenagree].[tenancy_rent] AS [t1_c146] FROM [dbo].[tenagree] [tenagree]

WHERE (t.house_ref=tenagree.house_ref) AND ([tenagree].[house_ref]=:ypl0)



whoohoo…now got it working…




public function relations()

{

   return array(

    'agreements' => array(self::HAS_MANY, 'agreements', 'person_ref', 'on' => 'person_ref=agreements.person_ref','alias' => 'agreements')

   );

}



I had to omit the ‘t’ in t.person_ref…

Thanks PoL

EDIT.

I should also not that I had to specify the primay keys in my model




public function primaryKey()

	{

		return array('person_ref','id');

	}