Table join confusion...

[jakobjp]

I need a little assistance with a join over 3 tables.

I have the following Models (User > Profile > Infogroup >
Information):
- User (hasMany Profile)
- Profile (belongsTo User, hasMany Infogroup)
- Infogroup (belongsTo Profile, hasMany Information)
- Information (belongsTo Infogroup)

The relationships are defined in the model classes, and there is no
problem getting the associated data, when for example fetching a User.

However, what do I need to do to get all Information that belongs to a
specific User?

I.e. all rows from the informations table "where
Information.infogroup_id = Infogroup_id AND Infogroup.profile_id =
Profile.id AND Profile.user_id = XYZ"?

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

[Thiago Nuic Vidigal]

You can include other tables in the query using the 'joins' option of the Model->find method.

On Sat, Nov 7, 2009 at 4:48 PM, jakobjp <mail@...> wrote:

I need a little assistance with a join over 3 tables.

I have the following Models (User > Profile > Infogroup >
Information):
- User (hasMany Profile)
- Profile (belongsTo User, hasMany Infogroup)
- Infogroup (belongsTo Profile, hasMany Information)
- Information (belongsTo Infogroup)

The relationships are defined in the model classes, and there is no
problem getting the associated data, when for example fetching a User.

However, what do I need to do to get all Information that belongs to a
specific User?

I.e. all rows from the informations table "where
Information.infogroup_id = Infogroup_id AND Infogroup.profile_id =
Profile.id AND Profile.user_id = XYZ"?

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

[kdubya]

On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@...> wrote:
> You can include other tables in the query using the 'joins' option of the
> Model->find method.
>

This sound like the solution to a problem I am trying to solve but I
have looked in the Manual and can find almost nothing about "joins"
option to "find()'. Can you give an example of a "find()" that uses
"joins"? I am looking for the specific syntax.

Thank you,
Ken

--

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

[Dr. Loboto]

http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find

Minimal code for join:
$this->find(
    'all',
    array (
        'joins' => array (
            array (
                'table' => 'my_table',
                'alias' => 'MyTable',
                'conditions' => 'MyTable.foreign_key = '.$this-
>alias.'.id'
            )
        )
    )
);

On Nov 19, 2:24 am, kdubya <kenwin...@...> wrote:

> On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@...> wrote:
>
> > You can include other tables in the query using the 'joins' option of the
> > Model->find method.
>
> This sound like the solution to a problem I am trying to solve but I
> have looked in the Manual and can find almost nothing about "joins"
> option to "find()'. Can you give an example of a "find()" that uses
> "joins"? I am looking for the specific syntax.
>
> Thank you,
> Ken

--

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

[joshs.silverman]

Hello,

Have you set recursive to 2 in your find options array (http://
book.cakephp.org/view/439/recursive)? When querying your User model,
that should get you the profile and infogroups.

As for joining Information to your infogroups, I am not sure if
recursive = 2 will automatically join to that depth. Try binding
Information to the Infogroup model before querying. When working from
the User model:

            $this->Profile->Infogroup->bindModel(
                array('hasMany' => array('Information'))
            );

Since this will take you to three levels of recursion, you might also
want to unbind any extra models.

Let me know what works for you. I have never needed to join past
recursive = 2.

Good luck.


On Nov 18, 3:24 pm, kdubya <kenwin...@...> wrote:

> On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@...> wrote:
>
> > You can include other tables in the query using the 'joins' option of the
> > Model->find method.
>
> This sound like the solution to a problem I am trying to solve but I
> have looked in the Manual and can find almost nothing about "joins"
> option to "find()'. Can you give an example of a "find()" that uses
> "joins"? I am looking for the specific syntax.
>
> Thank you,
> Ken

--

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

[kdubya]

Dr. Lobato,

Thank you. I don't know why I didn't find this article on the bakery
but it looks just what I need. Your example code is very clear. I'll
try it today.

Ken

On Nov 18, 10:51 pm, "Dr. Loboto" <drlob...@...> wrote:
> http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
<snip>

--

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

[Dave-421]

Probably because if you follow naming convention cake handles all of the necessary joins for you behind the scenes.  You should definitely check your recursive value as the previous poster mentioned as well as your database relationships set in your model.  Usually User hasOne Profile I would think, so with hasMany you may get quite a few profile results depending on your find call.

On Fri, Nov 20, 2009 at 8:45 AM, kdubya <kenwinans@...> wrote:

Dr. Lobato,

Thank you. I don't know why I didn't find this article on the bakery
but it looks just what I need. Your example code is very clear. I'll
try it today.

Ken

On Nov 18, 10:51 pm, "Dr. Loboto" <drlob...@...> wrote:
> http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
<snip>

--

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

--

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

[joshs.silverman]

@kdubya

I was curious about recursions past a depth of 2 so I ran some tests
on a data model I'm currently working with. Apparently you can set
recursive as high as you want. In your case you would want $this-
>recursive = 3 (when writing in the context of your model)

Be sure to unbind unnecessary model relations! Cake will not only join
to whatever depth you want, but it will also rejoin domains so you'd
have information repeated in your resulting array (if you don't unbind
the the belongsTo relation going in the other direction).

If you get your model relations correct, this is a REALLY easy way to
achieve what you are looking for.

Josh

On Nov 20, 2:52 pm, Dave <davidcr...@...> wrote:

> Probably because if you follow naming convention cake handles all of the
> necessary joins for you behind the scenes.  You should definitely check your
> recursive value as the previous poster mentioned as well as your database
> relationships set in your model.  Usually User hasOne Profile I would think,
> so with hasMany you may get quite a few profile results depending on your
> find call.
>
>
>
> On Fri, Nov 20, 2009 at 8:45 AM, kdubya <kenwin...@...> wrote:
> > Dr. Lobato,
>
> > Thank you. I don't know why I didn't find this article on the bakery
> > but it looks just what I need. Your example code is very clear. I'll
> > try it today.
>
> > Ken
>
> > On Nov 18, 10:51 pm, "Dr. Loboto" <drlob...@...> wrote:
> > >http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
> > <snip>
>
> > --
>
> > You received this message because you are subscribed to the Google Groups
> > "CakePHP" group.
> > To post to this group, send email to cake-php@....
> > To unsubscribe from this group, send email to
> > cake-php+unsubscribe@...<cake-php%2Bunsubscribe@... om>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/cake-php?hl=.

--

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

[joshs.silverman]

whoops. I meant to address that last comment to jakobjp

On Nov 20, 11:08 pm, Josh <joshs.silver...@...> wrote:

> @kdubya
>
> I was curious about recursions past a depth of 2 so I ran some tests
> on a data model I'm currently working with. Apparently you can set
> recursive as high as you want. In your case you would want $this-
>
> >recursive = 3 (when writing in the context of your model)
>
> Be sure to unbind unnecessary model relations! Cake will not onlyjoin
> to whatever depth you want, but it will also rejoin domains so you'd
> have information repeated in your resulting array (if you don't unbind
> the the belongsTo relation going in the other direction).
>
> If you get your model relations correct, this is a REALLY easy way to
> achieve what you are looking for.
>
> Josh
>
> On Nov 20, 2:52 pm, Dave <davidcr...@...> wrote:
>
>
>
> > Probably because if you follow naming convention cake handles all of the
> > necessary joins for you behind the scenes.  You should definitely check your
> > recursive value as the previous poster mentioned as well as your database
> > relationships set in your model.  Usually User hasOne Profile I would think,
> > so with hasMany you may get quite a few profile results depending on your
> > find call.
>
> > On Fri, Nov 20, 2009 at 8:45 AM, kdubya <kenwin...@...> wrote:
> > > Dr. Lobato,
>
> > > Thank you. I don't know why I didn't find this article on the bakery
> > > but it looks just what I need. Your example code is very clear. I'll
> > > try it today.
>
> > > Ken
>
> > > On Nov 18, 10:51 pm, "Dr. Loboto" <drlob...@...> wrote:
> > > >http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-...
> > > <snip>
>
> > > --
>
> > > You received this message because you are subscribed to the Google Groups
> > > "CakePHP" group.
> > > To post to this group, send email to cake-php@....
> > > To unsubscribe from this group, send email to
> > > cake-php+unsubscribe@...<cake-php%2Bunsubscr...@... om>
> > > .
> > > For more options, visit this group at
> > >http://groups.google.com/group/cake-php?hl=.

--

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