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

Improve this question
1
  • 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 Commented Jul 30, 2020 at 12:00

2 Answers 2

Reset to default
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
3
  • 1
    Worth to mention the identity map pattern ? Commented Jul 30, 2020 at 13:48
  • 1
    @Christophe that sounds like my final paragraph Commented Jul 30, 2020 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. Commented Jul 30, 2020 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
2
  • 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. Commented Jul 30, 2020 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? Commented Jul 30, 2020 at 13:39

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.