title: How can I "iterate" over a query in a view?
body: <p>I have the the following SQL query.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      us.foo,
      us.user_id_2,
      (
          SELECT COUNT(*)
          FROM my_table x
          WHERE x.foo &gt;= us.foo
              AND x.user_id_2 = 53
      ) AS position
  FROM my_table us
  WHERE us.user_id_1 = 53
  ORDER BY position;
  </code></pre>
  <p>This gives me results like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>foo</th>
  <th>user_id_2</th>
  <th>position</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>42</td>
  <td>687</td>
  <td>0</td>
  </tr>
  <tr>
  <td>40</td>
  <td>9832</td>
  <td>1</td>
  </tr>
  <tr>
  <td>39</td>
  <td>12</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This can be interprted as follows: User <code>687</code> is the first match for user <code>53</code> with score <code>42</code>. User <code>9832</code> is the second match with score <code>49</code> and so on.
  My problem is that the user is <code>53</code> is hardcoded in the query.
  I need the results for every user in the <code>users</code> table.
  To be more precisely, I want something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>foo</th>
  <th>user_id_1</th>
  <th>user_id_2</th>
  <th>position</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>42</td>
  <td>53</td>
  <td>687</td>
  <td>0</td>
  </tr>
  <tr>
  <td>40</td>
  <td>53</td>
  <td>9832</td>
  <td>1</td>
  </tr>
  <tr>
  <td>39</td>
  <td>53</td>
  <td>12</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  <tr>
  <td>193</td>
  <td>12</td>
  <td>53</td>
  <td>0</td>
  </tr>
  <tr>
  <td>175</td>
  <td>12</td>
  <td>9832</td>
  <td>1</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I basically want is iterating over <code>SELECT id FROM users</code> use that id instead my hardcoded <code>53</code> from my query.</p>
  <p>Ordering does not matter here. I'm using MariadDb 10.3.</p>
  <p>How can I do this?</p>
  <p>The structure of <code>my_table</code> is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>user_id_1</th>
  <th>user_id_2</th>
  <th>foo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>53</td>
  <td>687</td>
  <td>42</td>
  </tr>
  <tr>
  <td>687</td>
  <td>53</td>
  <td>42</td>
  </tr>
  <tr>
  <td>53</td>
  <td>9832</td>
  <td>40</td>
  </tr>
  <tr>
  <td>9832</td>
  <td>53</td>
  <td>40</td>
  </tr>
  <tr>
  <td>53</td>
  <td>9832</td>
  <td>39</td>
  </tr>
  <tr>
  <td>9832</td>
  <td>53</td>
  <td>39</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>