MongoDB / SubQuery

[Daniele Tassone]

Hi at all,
I'm building an gaming based on the MongoDB. Today i need to
understand how i can build subquery.

Collection 1 (User):
- id
- name

Collection 2 (Point):
- UserId
- Score (Int32)

Basically, i need to select all User and order by Score Descending.

I know that i can do it better if a move "Score" from Collection2 in
Collection1,
but i Think that there are scenario where it is not possibile to
aggregate 2 collections.

Daniele

[Andreas Jung]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No joins - you need to perform indidivual queries and do whatever
is needed on the application level.

- -aj

- --
ZOPYX Limited | zopyx group
Charlottenstr. 37/1 | The full-service network for Zope & Plone
D-72070 T�bingen | Produce & Publish
www.zopyx.com | www.produce-and-publish.com
- ------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJPKPALAAoJEADcfz7u4AZjqEALvA/WvZTr7+WEp8RPvpRyuicd
SqVX7mak8ZLuU3w6N1fsSV2ED8rAZGEMMUiq6DCSQYcbCXK2yUbKfVCPZQGqT9GZ
a5v0ZVdVkryoUAfqmBosM+HKN5tTx2WGMrRo3CY+XFX5wyhIKQ4SFPJGB3R7fHSB
UEBZCB8sily5me6+OpesQ6RGavh1+KgNtjpcx2RXbjgyH7E3R3l+FRWfHl3KT83H
0IefImN22CxwIVQS7tND4HmDBjoRkmSPHd7B+IDT7lGVXMI6uhVbgbhTL8PRz9uo
u2TIbzilA0qyTOL2/WKW6P3EcSd2mWb6FZ79FR4Ifi3LJeGrWW0HLIHHPOmCaAEa
jenrtWdOXv8n0BZFlF77C2lojSFAixeo2IVSZaM8zk3yWGXKWzVZ8HHzsplmJR7i
NpNpUROokhpbt0Jhu4r79i4VGWJCI8ZVPz7RmZoozhWOP/gt+96Q2IuA8502SYuK
TulX3O0mmsPCq1QcK3O08DiH7ZLQTQE=
=rwKd
-----END PGP SIGNATURE-----

[Nat]

MongoDB doesn't support join and it tends to stay that way. If you want to join, you can do that on the client side. 

For your particular case, it probably makes more sense to store score in the user collection as you mentioned.

[Adam C]

Daniele,

As mentioned by others, there are no joins in MongoDB, it is covered
in general terms here:

http://www.mongodb.org/display/DOCS/Schema+Design#SchemaDesign-EmbeddingandLinking

Given the simple example you have here, it would seem that moving
Score into your user collection is the way to go, but of course you
may have greatly simplified the model to ask your question. At some
point in your schema design you need to make a decision as to whether
embedding or linking (and the extra queries associated with it) is the
way to go, and only you can make that call based on your knowledge of
the data in your application.

Adam.

On Feb 1, 7:50 am, Daniele Tassone <daniele.tassone2...@gmail.com>
wrote:

[Daniele Tassone]

I know that theare are no Join.
Just to ask If anyone have found different solution than Embed the
'score' into 'User'.

1) With Eval, 50K document User can be merged with 50K document Score
at Server Side,
and after what ordered by Score.
2) Without Eval, i need to transfer 50K user to Clients.

The best method that i can use is to work with Eval, right?

Daniele

[Eliot Horowitz]

What's the exact output you want?
You can always just put the username in scores.
And do you really need a list of 50k?
If you just want the top 100, then I would just query from the client.

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

[Sam Millman]

"The best method that i can use is to work with Eval, right?"

Hell no. If you really require 50K an MR would be best (possibly, although transfering to client side and doing it there is not much a problem for most high level languages), never eval.

[Daniele Tassone]

Hi Eliot,

Yes, TOP 100 User ordered By Score.

As you say, i need to do:
1) Select top 100 Score ordered by Score.Point
2) Select 100 User (Score.UserID) (i can use Query.In to do this)
3) Match the 2 value and return 100 User at Client-Web

Another Query that i need to do is:

Top 100 User Where User.country=YY Order by Score.Point.

1) Select top 100 Score ordered by Score.Point
2) Problem: But i need only the Score.Point in the coutry User.Country
(how i can do it?)
3) Now i have the 100 Score ordered by Score.Point in the
User.Country=yy
4) Match the 2 value and return 100 User at Client-Web

Daniele

[Sam Millman]

"Yes, TOP 100 User ordered By Score.

As you say, i need to do:
1) Select top 100 Score ordered by Score.Point
2) Select 100 User (Score.UserID) (i can use Query.In to do this)
3) Match the 2 value and return 100 User at Client-Web"

To do this I would start by doing one query to get top 100 scores aggregated on user (since I assume your score document has a user_id in it).

After scroll through 100 score rows and pick out the _id, concat the _ids into one array and do a $in on the user collection.

Bam! no eval or embedding or MR :)

[Daniele Tassone]

Yes, and How about the Second type of query that i have write?

[Sam Millman]

Personally I would solve it through schema.

Place the country field on the score point.

That is something you would do in SQL as well to prevent having to do such a huge join so this is no different to strategies observed in SQL really.

[Sam Millman]

By placing country field on score point I mean to house it on both the user and score point.

[Ge Hui]

There is a data like this:{"_id":3, "pack" : [{"id" : 2001 "cnt" :
2 }, { "id" : 2002,"cnt" : 3}], "info":"Hello!"}my query condition is
{"$and":[{"_id":3},{"pack.id":2001}]}field condition is {"pack":1}The
query result is : {"_id":3, "pack" : [{"id" : 2001 "cnt" : 2 },
{ "id" : 2002,"cnt" : 3}]}But I just want to get the result : {"_id":
3, "pack" : [{"id" : 2001 "cnt" : 2 }]}How should I write the field
condition?

[Sam Millman]

That does not look related to this question however:

"{"$and":[{"_id":3},{"pack.id":2001}]}"

Not sure why you are using and $and there, since and is assumed by default.

You result will be: ""_id":3, "pack" : [{"id" : 2001 "cnt" : 2 }, { "id" : 2002,"cnt" : 3}]}" Because Mongo does not yet support positional return. You can watch the JIRA here: https://jira.mongodb.org/browse/SERVER-828

Should be fixed soon though. For the moment you will need to filter the results on the client side in your code.