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"?
On Sat, Nov 7, 2009 at 4:48 PM, jakobjp <m...@jakobjp.net> 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"?
On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@gmail.com> 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.
> On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@gmail.com> 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.
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:
> On Nov 7, 2:35 pm, Thiago Nuic Vidigal <tvidi...@gmail.com> 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. 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...@gmail.com> 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...@winanstech.com> 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.
> 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<cake-php%2Bunsubscribe@googlegroups.c om>
> .
> For more options, visit this group at
> http://groups.google.com/group/cake-php?hl=.
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...@gmail.com> 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...@winanstech.com> 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.
> > 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<cake-php%2Bunsubscr...@googlegroups.c om>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/cake-php?hl=.
> 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...@gmail.com> 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...@winanstech.com> 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.
> > > 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<cake-php%2Bunsubscr...@googlegroups.c om>
> > > .
> > > For more options, visit this group at
> > >http://groups.google.com/group/cake-php?hl=.
|
