You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Nishant Neeraj <ni...@gmail.com> on 2010/06/08 16:41:39 UTC
querying vs using relationship list and iterate
Hi,
I have three tables USERS, MEMBERSHIPS, GROUPS -- where membership is a
linking table that joins users and groups in many-to-many relationship, and
also stores users' role in the group.
Sometime when I need to know whether a user exists in a group [or what role
a user has in a group], I can do it by two ways:
1. groups.getMembershipList() and iterate over the list inflating each
member and comparing with the User object that is on test. like
for(Membership m: g.getMembershipList())
{
if(m.getUser().equals(testUser)
return true;
}
Or.
2. I can just write a query using Expression.
Expression.forString(Membership.class, "user = $testUser and group=$group");
Can someone suggest which one is better approach? Or, is there a even better
third approach?
Thanks
Nishant
Re: querying vs using relationship list and iterate
Posted by Nishant Neeraj <ni...@gmail.com>.
Ah, great. Thanks. Exactly, what I needed to know.
On Tue, Jun 8, 2010 at 9:02 PM, Michael Gentry <mg...@masslight.net>wrote:
> Both require a trip to the DB.
>
> #1 requires a trip one time to the DB to fault the groups from the DB,
> but then it'll be in memory.
>
> #2 requires a trip to the DB every time, so that could be slower in
> the long run even though it returns fewer matches.
>
> If this is something that only happens once and you are REALLY
> concerned about performance (and possibly have a LOT of groups), I'd
> go with #2, otherwise #1. You can optimize #1 a bit, too, so you
> don't have to iterate each time to check.
>
> mrg
>
>
> On Tue, Jun 8, 2010 at 11:11 AM, Nishant Neeraj
> <ni...@gmail.com> wrote:
> > Thanks Michael for the quick response.
> >
> > I was wondering which one will be less costlier to do? A join query(#2),
> or
> > iterating over objects(#1)? And isn't iterating over objects need to pull
> > each object from database before they can be compared?
> >
> > Thanks
> > Nishant
> >
> > On Tue, Jun 8, 2010 at 8:25 PM, Michael Gentry <mgentry@masslight.net
> >wrote:
> >
> >> #2 won't work the way you want. It would need to be more along the
> lines
> >> of:
> >>
> >> user = $testUser and user.memberships.group = $group
> >>
> >>
> >> I've tended to use something closer to #1, but encapsulate it in my
> >> User.java class as something like:
> >>
> >> public boolean isInGroup(String groupName)
> >> {
> >> for (Group g : getMemberships().getGroups())
> >> if (groupName.equals(g.getName())
> >> return true;
> >> return false;
> >> }
> >>
> >> That's pseudocode and unlikely to work, but should give you the
> >> general idea. You can then say:
> >>
> >> if (myUser.isInGroup(testGroupName)) {...}
> >>
> >>
> >> Thanks,
> >>
> >> mrg
> >>
> >>
> >> On Tue, Jun 8, 2010 at 10:41 AM, Nishant Neeraj
> >> <ni...@gmail.com> wrote:
> >> > Hi,
> >> >
> >> > I have three tables USERS, MEMBERSHIPS, GROUPS -- where membership is
> a
> >> > linking table that joins users and groups in many-to-many
> relationship,
> >> and
> >> > also stores users' role in the group.
> >> >
> >> > Sometime when I need to know whether a user exists in a group [or what
> >> role
> >> > a user has in a group], I can do it by two ways:
> >> >
> >> > 1. groups.getMembershipList() and iterate over the list inflating each
> >> > member and comparing with the User object that is on test. like
> >> >
> >> > for(Membership m: g.getMembershipList())
> >> > {
> >> > if(m.getUser().equals(testUser)
> >> > return true;
> >> > }
> >> >
> >> > Or.
> >> >
> >> > 2. I can just write a query using Expression.
> >> >
> >> > Expression.forString(Membership.class, "user = $testUser and
> >> group=$group");
> >> >
> >> >
> >> > Can someone suggest which one is better approach? Or, is there a even
> >> better
> >> > third approach?
> >> >
> >> > Thanks
> >> > Nishant
> >> >
> >>
> >
>
Re: querying vs using relationship list and iterate
Posted by Michael Gentry <mg...@masslight.net>.
Both require a trip to the DB.
#1 requires a trip one time to the DB to fault the groups from the DB,
but then it'll be in memory.
#2 requires a trip to the DB every time, so that could be slower in
the long run even though it returns fewer matches.
If this is something that only happens once and you are REALLY
concerned about performance (and possibly have a LOT of groups), I'd
go with #2, otherwise #1. You can optimize #1 a bit, too, so you
don't have to iterate each time to check.
mrg
On Tue, Jun 8, 2010 at 11:11 AM, Nishant Neeraj
<ni...@gmail.com> wrote:
> Thanks Michael for the quick response.
>
> I was wondering which one will be less costlier to do? A join query(#2), or
> iterating over objects(#1)? And isn't iterating over objects need to pull
> each object from database before they can be compared?
>
> Thanks
> Nishant
>
> On Tue, Jun 8, 2010 at 8:25 PM, Michael Gentry <mg...@masslight.net>wrote:
>
>> #2 won't work the way you want. It would need to be more along the lines
>> of:
>>
>> user = $testUser and user.memberships.group = $group
>>
>>
>> I've tended to use something closer to #1, but encapsulate it in my
>> User.java class as something like:
>>
>> public boolean isInGroup(String groupName)
>> {
>> for (Group g : getMemberships().getGroups())
>> if (groupName.equals(g.getName())
>> return true;
>> return false;
>> }
>>
>> That's pseudocode and unlikely to work, but should give you the
>> general idea. You can then say:
>>
>> if (myUser.isInGroup(testGroupName)) {...}
>>
>>
>> Thanks,
>>
>> mrg
>>
>>
>> On Tue, Jun 8, 2010 at 10:41 AM, Nishant Neeraj
>> <ni...@gmail.com> wrote:
>> > Hi,
>> >
>> > I have three tables USERS, MEMBERSHIPS, GROUPS -- where membership is a
>> > linking table that joins users and groups in many-to-many relationship,
>> and
>> > also stores users' role in the group.
>> >
>> > Sometime when I need to know whether a user exists in a group [or what
>> role
>> > a user has in a group], I can do it by two ways:
>> >
>> > 1. groups.getMembershipList() and iterate over the list inflating each
>> > member and comparing with the User object that is on test. like
>> >
>> > for(Membership m: g.getMembershipList())
>> > {
>> > if(m.getUser().equals(testUser)
>> > return true;
>> > }
>> >
>> > Or.
>> >
>> > 2. I can just write a query using Expression.
>> >
>> > Expression.forString(Membership.class, "user = $testUser and
>> group=$group");
>> >
>> >
>> > Can someone suggest which one is better approach? Or, is there a even
>> better
>> > third approach?
>> >
>> > Thanks
>> > Nishant
>> >
>>
>
Re: querying vs using relationship list and iterate
Posted by Nishant Neeraj <ni...@gmail.com>.
Thanks Michael for the quick response.
I was wondering which one will be less costlier to do? A join query(#2), or
iterating over objects(#1)? And isn't iterating over objects need to pull
each object from database before they can be compared?
Thanks
Nishant
On Tue, Jun 8, 2010 at 8:25 PM, Michael Gentry <mg...@masslight.net>wrote:
> #2 won't work the way you want. It would need to be more along the lines
> of:
>
> user = $testUser and user.memberships.group = $group
>
>
> I've tended to use something closer to #1, but encapsulate it in my
> User.java class as something like:
>
> public boolean isInGroup(String groupName)
> {
> for (Group g : getMemberships().getGroups())
> if (groupName.equals(g.getName())
> return true;
> return false;
> }
>
> That's pseudocode and unlikely to work, but should give you the
> general idea. You can then say:
>
> if (myUser.isInGroup(testGroupName)) {...}
>
>
> Thanks,
>
> mrg
>
>
> On Tue, Jun 8, 2010 at 10:41 AM, Nishant Neeraj
> <ni...@gmail.com> wrote:
> > Hi,
> >
> > I have three tables USERS, MEMBERSHIPS, GROUPS -- where membership is a
> > linking table that joins users and groups in many-to-many relationship,
> and
> > also stores users' role in the group.
> >
> > Sometime when I need to know whether a user exists in a group [or what
> role
> > a user has in a group], I can do it by two ways:
> >
> > 1. groups.getMembershipList() and iterate over the list inflating each
> > member and comparing with the User object that is on test. like
> >
> > for(Membership m: g.getMembershipList())
> > {
> > if(m.getUser().equals(testUser)
> > return true;
> > }
> >
> > Or.
> >
> > 2. I can just write a query using Expression.
> >
> > Expression.forString(Membership.class, "user = $testUser and
> group=$group");
> >
> >
> > Can someone suggest which one is better approach? Or, is there a even
> better
> > third approach?
> >
> > Thanks
> > Nishant
> >
>
Re: querying vs using relationship list and iterate
Posted by Michael Gentry <mg...@masslight.net>.
#2 won't work the way you want. It would need to be more along the lines of:
user = $testUser and user.memberships.group = $group
I've tended to use something closer to #1, but encapsulate it in my
User.java class as something like:
public boolean isInGroup(String groupName)
{
for (Group g : getMemberships().getGroups())
if (groupName.equals(g.getName())
return true;
return false;
}
That's pseudocode and unlikely to work, but should give you the
general idea. You can then say:
if (myUser.isInGroup(testGroupName)) {...}
Thanks,
mrg
On Tue, Jun 8, 2010 at 10:41 AM, Nishant Neeraj
<ni...@gmail.com> wrote:
> Hi,
>
> I have three tables USERS, MEMBERSHIPS, GROUPS -- where membership is a
> linking table that joins users and groups in many-to-many relationship, and
> also stores users' role in the group.
>
> Sometime when I need to know whether a user exists in a group [or what role
> a user has in a group], I can do it by two ways:
>
> 1. groups.getMembershipList() and iterate over the list inflating each
> member and comparing with the User object that is on test. like
>
> for(Membership m: g.getMembershipList())
> {
> if(m.getUser().equals(testUser)
> return true;
> }
>
> Or.
>
> 2. I can just write a query using Expression.
>
> Expression.forString(Membership.class, "user = $testUser and group=$group");
>
>
> Can someone suggest which one is better approach? Or, is there a even better
> third approach?
>
> Thanks
> Nishant
>