Select from many-to-many in one query

Question

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?

Answer 1

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.

Answer 2

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.