title: LoopBack: performance issue with relational databases
body: <p>In some situations, LoopBack seems to deal in a very inefficient way with relational databases. I've bumped into a performance issue and I was wondering if someone has found a way of working around it.</p>
  
  <p>To demostrate this, I've created a MySQL database with three tables and a simple many-to-many relationship, as shown below.</p>
  
  <p>This is a pretty standard and common situation (which by the way I borrowed straight from LoopBack's documentation).</p>
  
  <p><img src="https://i.stack.imgur.com/I4xo2.png" alt="A simple many-to-may relationship"></p>
  
  <p>Here's how I've populated the tables:</p>
  
  <pre><code>mysql&gt; SELECT * FROM physician;
  +----+----------+
  | id | name     |
  +----+----------+
  |  1 | Smith    |
  |  2 | Johnson  |
  |  3 | Williams |
  |  4 | Jones    |
  +----+----------+
  
  mysql&gt; SELECT * FROM patient;
  +----+----------+
  | id | name     |
  +----+----------+
  |  1 | Anderson |
  |  2 | Jackson  |
  |  3 | White    |
  |  4 | Roberts  |
  |  5 | Lewis    |
  |  6 | Clark    |
  |  7 | Morgan   |
  +----+----------+
  
  mysql&gt; SELECT * FROM appointment;
  +----+--------------+------------+------------+
  | id | physician_id | patient_id | date       |
  +----+--------------+------------+------------+
  |  6 |            1 |          1 | 2014-06-12 |
  |  7 |            1 |          3 | 2014-06-12 |
  |  8 |            1 |          5 | 2014-06-12 |
  |  9 |            2 |          2 | 2014-06-12 |
  | 10 |            2 |          4 | 2014-06-12 |
  | 11 |            3 |          7 | 2014-06-12 |
  | 12 |            2 |          2 | 2014-06-13 |
  +----+--------------+------------+------------+
  </code></pre>
  
  <p>Now, let's say we want to find the list of patients that have an appointment with Dr. Johnson (<code>physician_id = 2</code>). There are several sensible ways to achieve this, like:</p>
  
  <pre><code>SELECT * FROM patient WHERE id IN( SELECT patient_id FROM appointment WHERE physician_id = 2 )
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SELECT patient.* FROM patient JOIN appointment ON patient.id = appointment.patient_id WHERE appointment.physician_id = 2
  </code></pre>
  
  <p>What LoopBack does in the background is totally another story:</p>
  
  <pre><code>SELECT `id`, `physician_id`, `patient_id`, `date` FROM `appointment` WHERE `physician_id` = 2 ORDER BY `id`
  SELECT * FROM `patient` WHERE `id` = 2 LIMIT 1
  SELECT * FROM `patient` WHERE `id` = 4 LIMIT 1
  SELECT * FROM `patient` WHERE `id` = 2 LIMIT 1
  </code></pre>
  
  <p>What we see here is that it will first search for all doctor Johnson's appointments (first query) and then search the <code>patient</code> table for patients IDs found with the previous query (last 3 queries). Here's the result:</p>
  
  <pre><code>[
    {
      "id": 2,
      "name": "Jackson"
    },
    {
      "id": 4,
      "name": "Roberts"
    },
    {
      "id": 2,
      "name": "Jackson"
    }
  ]
  </code></pre>
  
  <p>This looks very inefficient. We can see 4 SQL queries above, which is effectively <code>1 + n</code> queries, where <code>n</code> is the number of patients! In another words, LoopBack will make MySQL scan the <code>patient</code> table as many times as many patients are found in the first query. Obviously this becomes extremely inefficient as the number of records in the patient table grows.</p>
  
  <p>Is there any other way I can achieve this more efficiently?</p>