migration

[Ranvir]

How to embed one collection into another based on some common fields, when both the collections are in mongodb.

[Jon Kern]

You need to be more clear, if you are asking a question and hope to get some answers.

What does the subject have to do with your one sentence post?

Jon Kern

blog: http://technicaldebt.com

twitter: http://twitter.com/JonKernPA

github: https://github.com/JonKernPA

On Oct 4, 2014, at 3:47 PM, Ranvir <sushan...@gmail.com> wrote:

How to embed one collection into another based on some common fields, when both the collections are in mongodb.

--
You received this message because you are subscribed to the Google
Groups "MongoMapper" group.
For more options, visit this group at
http://groups.google.com/group/mongomapper?hl=en?hl=en
---
You received this message because you are subscribed to the Google Groups "MongoMapper" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongomapper...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ranvir]

Hi Jon,
Thanks for the advice.
I have two collections in mongodb, order and orderdetails. The fields in order are orderNumber, orderDate, requireDate, shippedDate. The fields in orderdetails are orderNumber, productCode, quantityOrdered.

I wish to join order and orderdetails based on orderNumber such that first all fields of order should come and then all the fields of orderdetails in array, for each orderNumber.
Hope I can use mongomapper.

Looking forward to your help.

[Billy]

Once you start talking about joins, you are thinking back in SQL terms. Why not store the details of the order inside the order collection since that is how you expect to pull them out?

db.orders.find({}).first:

{
number: 123,

date: 2014-12-12,

require_date: 2014-12-14,

shipped_date: 2014-12-16,

details: [

 {code: 123, qty: 1},

 {code: 431, qty: 4},
 {code: 941, qty: 2},
 {code: 412, qty: 10},
 ]
}  

Also if this is for a real world app, I suggest also putting the price of the item at the time it was purchased, in the same place as your code and qty. That is something that can be different between two different days if there was a sale or price increase/reduction.

-BK

--

[Ranvir]

 Hi Bill,
Thank you for your reply. I am developing a method of migrating sql database to mongodb. The size is in range 20GB. I have used the database http://www.mysqltutorial.org/mysql-sample-database.aspx. My plan is to transfer the mysql tables as csv and the mongoimport to mongodb. I will get all separate collections and wish to embed all the collections in one.
If I understand your reply properly, you direct me to write a query to get the desired output. Can't we merge the collections using monomapper.
Pl. help.

[Billy]

Hrm. The problem I see with the link that you provided to the MySQL schema that you are using, is that it is not very ActiveRecord friendly. ActiveRecord SQL conventions are that you name your foreign key in a certain manner (singluar_tablename_id or order_id in your case, instead of orderNumber, in the order_details table). I think you're going to run in to some more issues with using that sort of naming scheme, and should use caution, or you will spin your wheels on problems that only exist because you're fighting convention.

That being said, there are ways around unconventionally named tables/collections.

It seems like what you want is to do:

class OrderDetail

  include MongoMapper::Document

  key :orderNumber, String

  key :productCode, String

  key :quantityOrdered, Integer

end

class Order
  include MongoMapper::Document

    key :orderNumber, String

    key :orderDate, Time

    key :requireDate, Time

    key :shippedDate, Time

    many :order_details, :foreign_key => :orderNumber
end

I haven't tested this but I think if it doesn't work it should at least get you on the correct path to start looking at mongomapper associations, and specifying alternate names for the foreign key. Otherwise it will follow ActiveRecord convention and expect the foreign key on order detail to be named order_id. Hope this helps. Have a good weekend!

-BK

[Jamie Orchard-Hays]

I would take this a step further.

Embed your OrderDetails into your Orders. Also, I would convert the names to the Rails convention:

class OrderDetail

  include MongoMapper::EmbeddedDocument

  key :order_number, String

  key :product_code, String

  key :quantity_ordered, Integer

end

class Order 

  include MongoMapper::Document

  key :order_number, String

  key :order_date, Time

  key :require_date, Time

  key :shipped_date, Time

  many :order_details

end

This is a more typical way of organizing this sort of data in Mongo. You'll get better performance (only one query) and it's easy to query by order_details if needed. If you need to export data, it's all in one collection.

You'll find in a Mongo db that you have fewer collections than you would tables in a Relational SQL db. If not, you're likely either using Mongo poorly or should be using a Relational SQL db anyway.

In terms of migrating your data, you'll just need a script that pulls data from your Relational DB converts the data into Mongo data structures and dumps them in Mongo. I'd skip Rails and MongoMapper for this. Just a simple script in your favorite language that can stream or batch your data.

Pick your favorite Mongo driver (I'd guess Ruby since you're moving to MM) and use that. 

Cheers,

Jamie

[Sushant Prusty]

Hi Jamie,

Thank you very much for your solution. Will get back to you.

Regards,

You received this message because you are subscribed to a topic in the Google Groups "MongoMapper" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongomapper/p1yx2WT1R_w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongomapper...@googlegroups.com.