How can I use embed to replace an id field with entire object from other collection

[mandeepm91]

My transation looks like this:

table "doctorcategory" do
    column "ID", :key, :as => :integer, :rename_to => "id", :references => "doctor"
    column "Title", :string, :rename_to => "title"
    column "Disabled", :boolean, :rename_to => "disabled"
end

table "doctor" do
    column "ID", :key, :as => :integer, :rename_to => "id"
    column "Signature", :text, :rename_to => "signature"
    column "RegNo", :string, :rename_to => "regNo"
    column "FirstName", :string, :rename_to => "firstName"
    column "LastName", :string, :rename_to => "lastName"
    column "doctorcategory_ID", :integer, :references => "doctorcategories", :rename_to => "doctorcategory"
    column "Qualification", :text, :rename_to => "qualification"
    column "WorkingSince", :integer, :rename_to => "workingSince"
    column "Availability", :boolean, :rename_to => "availability"
    column "Email", :string, :rename_to => "email"
    column "Phone", :string, :rename_to => "phone"
    column "OSType", :string, :rename_to => "osType"
    column "Hospital", :text, :rename_to => "hospital"
    column "Gender", :string, :rename_to => "gender"
    column "AuthCode", :string, :rename_to => "authCode"
    column "DocImage", :text, :rename_to => "docImage"
    column "RegId", :string, :rename_to => "regId"
    column "lastcall", :timestamp, :rename_to => "lastcall"
end

Column doctorcategory_ID in table doctor refers to the doctorcategory table via id field. Now in my output, I want the doctorcategory record to be embedded in the doctor record.

How can I achieve this ?

[anlek]

Hmm, it's a wired approach you have. I would say do something like this:

table "doctorcategory", rename_to: :doctor_categories do
    column "ID", :rename_to => "doctor_id", :references => "doctors"
    column "Title", :string, :rename_to => "title"
    column "Disabled", :boolean, :rename_to => "disabled"
end

table "doctor", rename_to: :doctors do
    column "ID", :key, :as => :integer, :rename_to => "id"
    column "Signature", :text, :rename_to => "signature"
    column "RegNo", :string, :rename_to => "regNo"
    column "FirstName", :string, :rename_to => "firstName"
    column "LastName", :string, :rename_to => "lastName"
    column "doctorcategory_ID", :integer, :references => "doctor_categories", :rename_to => "doctorcategory_id" 
    # ^^ Is this a has_one association? I confused, cause in your original post, this would refer to it's own (doctor) id column...
    column "Qualification", :text, :rename_to => "qualification"
    column "WorkingSince", :integer, :rename_to => "workingSince"
    column "Availability", :boolean, :rename_to => "availability"
    column "Email", :string, :rename_to => "email"
    column "Phone", :string, :rename_to => "phone"
    column "OSType", :string, :rename_to => "osType"
    column "Hospital", :text, :rename_to => "hospital"
    column "Gender", :string, :rename_to => "gender"
    column "AuthCode", :string, :rename_to => "authCode"
    column "DocImage", :text, :rename_to => "docImage"
    column "RegId", :string, :rename_to => "regId"
    column "lastcall", :timestamp, :rename_to => "lastcall"
end

Hopefully this will get you a bit closer. I haven't worked on Mongify in a while, I don't recall if singular table names cause issues. I mostly targeted rails conventions for naming tables. There is a shortage of testing other methodologies.

Good luck!

[mandeepm91]

Thanks for the reponse @anlek .. Sorry I could not explain the situation clearly. I will try again.

The id column of doctorcategory table identifies the category, not the doctor. Each doctorcategory record has on id For example

id	title	disabled
1	General Physician	false
2	Orthopaedic	false
3	TestCategory	true

And the doctor column contains all the doctor details as well as a doctorcategory column which refers to the id column of doctorcategory table. For example,

id	RegNo	firstName	doctorcategory
1	12213	Mr X	1
2	11223	Mr Y	1
3	1234123	Mr Z	2

This means that Mr X is a general physician since its doctorcategory column is referring to id = 1, which belongs to General Physician in doctorcategory table. Now my requirement is to embed the doctorcategory details into the doctor table so that I dont have to join on the id field

[anlek]

Right so you don't want to reference doctor_id in the doctorCategories table.

table "doctorcategory", rename_to: :doctor_categories do
    column "ID", :key, :rename_to => "id" #specifiying key will make id become an BSON::ObjectID
    column "Title", :string, :rename_to => "title"
    column "Disabled", :boolean, :rename_to => "disabled"
end

table "doctor", rename_to: :doctors do
    column "ID", :key, :as => :integer, :rename_to => "id"
    column "Signature", :text, :rename_to => "signature"
    column "RegNo", :string, :rename_to => "regNo"
    column "FirstName", :string, :rename_to => "firstName"
    column "LastName", :string, :rename_to => "lastName"
    column "doctorcategory_ID", :integer, :references => "doctor_categories", :rename_to => "doctorcategory_id" 
    column "Qualification", :text, :rename_to => "qualification"
    column "WorkingSince", :integer, :rename_to => "workingSince"
    column "Availability", :boolean, :rename_to => "availability"
    column "Email", :string, :rename_to => "email"
    column "Phone", :string, :rename_to => "phone"
    column "OSType", :string, :rename_to => "osType"
    column "Hospital", :text, :rename_to => "hospital"
    column "Gender", :string, :rename_to => "gender"
    column "AuthCode", :string, :rename_to => "authCode"
    column "DocImage", :text, :rename_to => "docImage"
    column "RegId", :string, :rename_to => "regId"
    column "lastcall", :timestamp, :rename_to => "lastcall"
end

That should work for you. Let me know how it turns out!

[mandeepm91]

This is creating a reference like this:

{
    "_id" : ObjectId("560e2d81a7444011fc001296"),
    "signature" : "",
    "regNo" : "47217",
    "firstName" : "Dr X",
    "lastName" : " ",
    "doctorcategory_id" : ObjectId("560e2d81a7444011fc001290"),
    "qualification" : "MBBS, MRCP(CH)",
    "workingSince" : 1989,
    "availability" : true,
    "email" : "[email protected]",
    "phone" : "+91xxxxxxxxx93",
    "osType" : "Android",
    "hospital" : "Medical Advisor",
    "gender" : "Female",
    "authCode" : "fr50e11f2a7ee2748e04",
    "regId" : "YCK3JqLJOpYuFiIpMEjYvPR4WY-1hKM__riM1leehHfe7EVjAspoDoA9zzOpA7vnnA9h6TUNfX",
    "lastcall" : ISODate("2015-08-27T05:38:35.000Z")
}

However, I need it to be like this:

{
    "_id" : ObjectId("560e2d81a7444011fc001296"),
    "signature" : "",
    "regNo" : "47217",
    "firstName" : "Dr X",
    "lastName" : " ",
    "doctorcategory_id" : {
        "_id" : ObjectId("560e2d81a7444011fc001290"),
        "title" : "Paediatricians",
        "disabled" : false    
    },
    "qualification" : "MBBS, MRCP(CH)",
    "workingSince" : 1989,
    "availability" : true,
    "email" : "[email protected]",
    "phone" : "+91xxxxxxxxx93",
    "osType" : "Android",
    "hospital" : "Medical Advisor",
    "gender" : "Female",
    "authCode" : "fr50e11f2a7ee2748e04",
    "regId" : "YCK3JqLJOpYuFiIpMEjYvPR4WY-1hKM__riM1leehHfe7EVjAspoDoA9zzOpA7vnnA9h6TUNfX",
    "lastcall" : ISODate("2015-08-27T05:38:35.000Z")
}

[anlek]

Yeah, Mongify does NOT embed by default. If you want to embed, try this:

table "doctorcategory", rename_to: :doctor_categories, embed_in: :doctors do
    column "ID", :key, :rename_to => "id" #specifiying key will make id become an BSON::ObjectID
    column "Title", :string, :rename_to => "title"
    column "Disabled", :boolean, :rename_to => "disabled"
end

[luannguyenQV]

I tried your helps, but it's not work correctly, if you embed "doctorcategory" on "doctors", only the number of "doctorcategory" embedded on "doctor", such as: "doctorcategory" has 10 rows and "doctor" has 30 rows, only 10 rows of "doctorcategory" embedded in to 10 rows of "doctor", and 20 rows don't have category. Please help.