Re: Self Join query

I typed this code out of the top of my head, but it might be worth a try :

        $this->User->Behaviors->attach('Containable');
        $this->User->bindModel(array(
            'hasOne' => array( // Model linking type is 'hasOne', this forces Cake to make joins
                'UserB' => array( // User other name than 'User' to avoid data arrays being mixed up in the result
                    'className' => 'User',
                    'foreignKey' => false, // Force conditions in query as defined below
                    'type' => 'LEFT',
                    'conditions' => array('User.company_id = UserB.company_id'))
        )));
        $result = $this->User->find('all', array('conditions' => array('User.id' => 2)));


Friendly greetings,
Bert

On Mon, Sep 28, 2009 at 1:43 PM, Bert Van den Brande <cyruzb@gmail.com> wrote:

I would use the Containable behavior here, combined with a custom binding that you attach on the fly.

Have no idea however how exactly to expres a self-join binding ... try and play around with it :)

On Mon, Sep 28, 2009 at 12:04 PM, hunny <saurabh85mahajan@gmail.com> wrote:

On Sep 28, 2:19 pm, Aivaras <faifas1...@gmail.com> wrote:
> Hey, take a look at this:http://voveris.eu/2009/09/05/left-join-with-cakephp/I am sure you find this
> handy.
>

> On Mon, Sep 28, 2009 at 12:04, hunny <saurabh85maha...@gmail.com> wrote:
>
> > Hi All,
>
> > I am new to cakephp. I would like to execute the following SELF Join
> > query.
>
> > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
> > ON A.id = 2 where A.company_id = B.company_id
>
> > Could some one guide or refer some tutorials, on how to achieve this
> > in cake php.
>
> > Thanks in advance....
>
>

Hi All,

Bad me, I think should have explained my problem properly.

Lets say following are the contents of my users table:

id | name | company id

1 | abc | 123
2 | def | 123
3 | ghi | 124
4 | jkl | 123

Now what I want is to retrieve, for a member (say 'abc') all the
people who are working in the same company.

Even though company_id is a foreign key, I am not interested in the
data of that table. What you suggested above is how to get the data
from the company table.

The simple sql query if I want all the members working in same company
as id = 2 would be:

SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
ON A.id = 2 where A.company_id = B.company_id

Since this includes JOIN with the table itself, I am not sure how to
achieve using Association Type.

--~--~---------~--~----~------------~-------~--~----~
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 cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---