• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

some problem migrating from SQL to NoSQL

 
David Spades
Ranch Hand
Posts: 348
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have 2 tables:
Agent and Property
One agent may handle several properties and one property may be handled by more than one agents.
in SQL, it's so easy because of joins operations, but how should I solve this problem with mongo 2.11?
thanks
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David Spades wrote:One agent may handle several properties and one property may be handled by more than one agents.

From this I guess it is a many-many relation.
I am a beginner in MongoDB, so I am not sure below is a sureshot solution.
I think it can be done by keeping ids of Agent in Property and ids of Property in Agent as array.
 
David Spades
Ranch Hand
Posts: 348
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply,
yes, that's one way, but this approach will require a join, which is not supported, so I'm still stuck.
thanks
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
 
David Spades
Ranch Hand
Posts: 348
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see, is this the recommended approach?, since breaking link will require updating multiple tables, unlike SQL paradigm, only one table needs updating. thanks
 
David Spades
Ranch Hand
Posts: 348
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and also there's another problem : let's say I go with the approach and I want to get only agents who handle at least one property, how do I do this using aggregate FW? so basically, I want to remove agents that's not present in property collection from the previous pipeline, which is Agent collection. Thanks
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David Spades wrote:breaking link will require updating multiple tables

David Spades wrote:I want to get only agents who handle at least one property

Yes I understand the problem, I will try to find suitable solution.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tapas Chand wrote:I think it can be done by keeping ids of Agent in Property and ids of Property in Agent as array.

I think this is probably the simplest solution here.

MongoDB does not support joins or foreign key constraints, but there's nothing to stop you running 2 single-collection queries instead of one join query. If you want to know which properties Bob is handling, fetch Bob's details, then take his list of property IDs and fetch the corresponding properties. If you want to know which agents are handling 23 Acacia Gardens, fetch the property details, then take its list of agent IDs and fetch the agent details.

Updates will be inconsistent (briefly) because you will have to update documents in both collections e.g when Bob gets a new property to handle, but that's part of the cost of using MongoDB: no transactions. There is a risk that over time the agent and property collections could become inconsistent e.g. because of failures during one of the updates. You might want to think about how to control this e.g. run queries to check updates succeeded, but that will depend on your needs.

EDIT:
You can also use the Write Concern to check if your individual update operations succeeded (e.g. when you update both of them at the same time), so that you can be more confident that your Agents and Properties collections are consistent.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can check the size of an array using the Aggregation framework e.g. to count the property IDs handled by each agent, then filter the results to exclude the rows you don't want.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Chris for making my life easy.
Otherwise I was trying to find another approach similar to "junction table".
But in Mongo DB, without relation, that will be quite an effort to maintain.
 
David Spades
Ranch Hand
Posts: 348
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
great, thanks for the help guys, really appreciate it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic