-2

A common issue I run into when building CRUD applications is how to retrieve many-to-many relationships from the database and put them into an object model.

Say, for example, I have the following data structure:

customers: 
  - name: bob
    items: 
      - chair
      - hat
  - name: steve
    items: 
      - hat
      - table

This is stored in the database like so:

customers
id | name
---|------
 0 | bob
 1 | steve

items
id | name
---|------
 0 | chair
 1 | table
 2 | hat

customers_items_link
customer_id | item_id
------------|--------
          0 | 0
          0 | 2
          1 | 2
          1 | 1

When it comes time to turn the data in the database back into objects in my programming language of choice, currently I'm pulling all three tables individually and processing them afterwards to create the objects and link them. Is there any way to reconstruct the objects in one query?

  • 2
    It's unclear what your problem is exactly. When you want a single query to return all rows from these 3 tables, you can simply select * from customers, items, customers_items_link – Rik D Jul 30 at 12:00
4

Yes.

You can issue a query that returns

customer_name | customer_id | item_id | item_name
--------------|-------------|---------|----------
bob           |           0 | 0       | chair
bob           |           0 | 2       | hat
steve         |           1 | 2       | hat
steve         |           1 | 1       | table

And then construct 2 Customer instances, and 3 Item instances, and add references to the items as appropriate in the customer.

That can be done in one pass over the data, if you hold a mapping of id -> object for both Customer and Item so that you don't create duplicates.

| improve this answer | |
  • 1
    Worth to mention the identity map pattern ? – Christophe Jul 30 at 13:48
  • 1
    @Christophe that sounds like my final paragraph – Caleth Jul 30 at 13:54
  • 1
    Indeed. At first reading I had the impression that you kept this mapping local to the single pass whereas the object already might be loaded from another query. – Christophe Jul 30 at 14:09
3

The problem you describe is known as the "n+1 select problem" and is referenced in ORM software. It would help if you dug into this question https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping

You either need to do a join and fetch duplicate data or do multiple selects. There are tradeoffs in both approaches.

| improve this answer | |
  • 1
    Ah, having the name of the problem helps. I was wondering why I hadn't seen more discussions about it on the internet. I'll look into this. – Omegastick Jul 30 at 13:01
  • 2
    interesting. The linked SO question mentions one-to-many. Isn’t there an additional challenge in the many-to-many where you have to avoid duplicating objects? – Christophe Jul 30 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.