php - how to do "INNER JOIN"-like query

[persten]

my mongodb:
--------------------------------------------------
users
{
"_id": "4ca30369fd0e910ecc000006",
"login": "user11",
"pass": "example_pass",
"date": "2010-09-29"
},
{
"_id": "4ca30373fd0e910ecc000007",
"login": "user22",
"pass": "example_pass",
"date": "2010-09-29"
}
--------------------------------------------------
news
{
"_id": "4ca305c2fd0e910ecc000003",
"name": "news 333",
"content": "news content 3333",
"user_id": "4ca30373fd0e910ecc000007",
"date": "2010-09-29"
},
{
"_id": "4ca305c2fd0e910ecc00000b",
"name": "news 222",
"content": "news content 2222",
"user_id": "4ca30373fd0e910ecc000007",
"date": "2010-09-29"
},
{
"_id": "4ca305b5fd0e910ecc00000a",
"name": "news 111",
"content": "news content",
"user_id": "4ca30369fd0e910ecc000006",
"date": "2010-09-29"
}
--------------------------------------------------
how to do next query using php:
SELECT n.*, u.*
FROM news AS n
INNER JOIN users AS u ON n.user_id = u.id
????

[Sam Millman]

Query both tables then merge array and array sort on user id (_id of the users collection)

--
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.

--
Bow Chicka Bow Wow

[Sam Millman]

Or (via nosql principles) change your model to reflect your querying.

users
{
 "_id": "4ca30369fd0e910ecc000006",
 "login": "user11",
 "pass": "example_pass",
 "date": "2010-09-29",

"news":[

{
 "_id": "4ca305c2fd0e910ecc000003",
 "name": "news 333",
 "content": "news content 3333",

 "date": "2010-09-29"
}

]
},

[PaulC]

I am not necessarily disagreeing, but why not just duplicate the
users._id field in the news collection? Then you can just do a simple
query. If the "news" items are small and few, who cares I guess.

I am in a similar situation where I have LOTS of news items and each
one could be a couple of megabytes, so thats why I prefer this method.

On Sep 30, 8:39 am, Sam Millman <smill...@nhbs.co.uk> wrote:
> Or (via nosql principles) change your model to reflect your querying.
>
> users
> {
>  "_id": "4ca30369fd0e910ecc000006",
>  "login": "user11",
>  "pass": "example_pass",
>  "date": "2010-09-29",
> "news":[
> {
>  "_id": "4ca305c2fd0e910ecc000003",
>  "name": "news 333",
>  "content": "news content 3333",
>  "date": "2010-09-29"
>
> }
> ]
> },
>

> On 30 September 2010 13:30, Sam Millman <smill...@nhbs.co.uk> wrote:
>
>
>
>
>
> > Query both tables then merge array and array sort on user id (_id of the
> > users collection)
>

> >> mongodb-user...@googlegroups.com<mongodb-user%2Bunsubscribe@google groups.com>
> >> .

[Sam Millman]

"I am not necessarily disagreeing, but why not just duplicate the
users._id field in the news collection? "

Joins allow for two pieces of information geographically dislocated, i.e. join the data in user with the data in videos on user id.

Presumably he needs more information than userid. He may well sort on user id but needs more information basically information from both tables, hence the join.

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.

[Colin M]

I recently implemented a basic experimental join-like helper in my PHP
ODM library (http://github.com/colinmollenhour/mongodb-php-odm). Usage
would look something like this in your case:

class Model_User extends Mongo_Document {
protected $name = 'users';
}
class Model_News extends Mongo_Document {
protected $name = 'news';
protected $_references = array('user' => array('model' => 'user',
'field' => 'user_id'));
}
$lastestNews = Mongo_Collection::factory('news')->sort_desc('date')-
>limit(10)->natural_join('user','user_id');
foreach($latestNews as $news){
echo "{$news->user->login} said {$news->content} on {$news->date}
\n";
}

I haven't tested it at all and I haven't implemented the other
direction (users->join('news')) either, but it shows that pseudo-joins
can be painless and still use only two queries total.

Thanks,
Colin

On Sep 30, 11:22 am, Sam Millman <smill...@nhbs.co.uk> wrote:
> "I am not necessarily disagreeing, but why not just duplicate the
> users._id field in the news collection? "
>
> Joins allow for two pieces of information geographically dislocated, i.e.
> join the data in user with the data in videos on user id.
>
> Presumably he needs more information than userid. He may well sort on user
> id but needs more information basically information from both tables, hence
> the join.
>

[GVP]

@persten: I don't want to sound callous, but the answer to your
question is basically "you don't". MongoDB is not a relational
database and is not intended to perform such relational queries.

Asking this question indicates that you may fundamentally
misunderstand how MongoDB works.

The simple answer to your question is that you'll need to write some
for loops. One to load the data from one collection and another to
load data from the second collection.

However, the more detailed answer is that you need to re-think your
queries. You give the following query which is bad for a couple of
reasons:

SELECT n.*, u.*
FROM news AS n
INNER JOIN users AS u ON n.user_id = u.id

1. There's no where clause. Why do you need every single news item
joined to every single user? What if you have 100M users and 2B news
items what are you going to do with this data? The where clause is
going to tell us something about how you want to query, it should be
there.
2. There are no specific columns. Why do you need a user's password
with every news item?

The standard solutions here are simple:
1. Store an array of News.ID inside of each user object.
2. Store some of the User Data inside of the News item.

If you can give us some better queries, then we can give you some
better answers.