Join a table to Itself with a join table

[BravoFoxtrot]

Hi All,

I've been reading other posts on this but I still can seem to make it
work.  This post seems to be what I want to do but I'm still having
trouble.
http://groups.google.com/group/cake-php/browse_thread/thread/6fea193fdd352916/5997925ec651fe1a?lnk=gst&q=join+to+itself+join+table#5997925ec651fe1a

I have a employees table:
CREATE TABLE employees (
    id integer NOT NULL,
    user_id integer,
    start_date date,
    termination_date date,
    vacation_rate numeric(15,2),
    is_supervisor boolean,
    full_time boolean,
    cont boolean,
    part_time boolean,
    part_time_amount numeric(15,2),
    on_leave boolean,
    birthday date
);

And join table employees_supervisors:
CREATE TABLE employees_supervisors (
    id integer NOT NULL,
    employee_id integer,
    supervisor_id integer
);

The relationship is that an employee can have multiple supervisors and
a supervisor can supervise many employees.  I read that the HABTM is
maybe not the best option, so I tried to make hasMany and belongsTo
releationships:

In Employee Model:
    var $hasMany = array(
        'Supervisors'=>array('className'=>'Employee',
                            'foreignKey'=>'employee_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
        )
    );

    var $belongsTo = array(
            'User' => array('className' => 'User',
                                'foreignKey' => 'user_id',
                                'conditions' => '',
                                'fields' => '',
                                'order' => ''
            ),

            'Supervisor'=>array('className'=>'EmployeesSupervisor',
                                'foreignKey' => 'id',
                                'conditions' =>
array('Employee.is_supervisor'=>true),
                                'fields' => '',
                                'order' => ''
            )
    );

In EmployeesSupervisor model:
    var $hasMany = array(
            'Employees' => array('className' => 'EmployeesSupervisor',
                                'foreignKey' => 'employee_id',
                                'conditions' => '',
                                'fields' => '',
                                'order' => ''
            )
    );

    var $belongsTo = array(
            'Supervisor' => array('className' => 'Employee',
                                'foreignKey' => 'id',
                                'conditions' => '',
                                'fields' => '',
                                'order' => ''
            )
    );


My problem right now is that when the joins occur it joins
"Employee"."id" = "Supervisor"."id" when what I want is
"Employee"."id" = "Supervisor"."employee_id" and/or "Employee"."id" =
"Supervisor"."supervisor_id".

I tried the associationForeignKey but I think that only works with
HABTM relationships.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---