Yii Framework Forum: Defining relationship between no primary key fields - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Defining relationship between no primary key fields How? Rate Topic: -----

#1 User is offline   _wk_ 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 22-January 10

Posted 22 April 2010 - 05:53 AM

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.
0

#2 User is offline   _wk_ 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 22-January 10

Posted 22 April 2010 - 08:52 AM

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.
0

#3 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 22 April 2010 - 09:24 AM

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')
  );
}

Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#4 User is offline   _wk_ 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 22-January 10

Posted 22 April 2010 - 09:31 AM

View PostPoL, on 22 April 2010 - 09:24 AM, said:

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)

0

#5 User is offline   _wk_ 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 22-January 10

Posted 22 April 2010 - 09:35 AM

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');
	}

This post has been edited by kevinb: 22 April 2010 - 09:37 AM

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users