Answers.Body: <p>I'll be happy to see the SQL answer if anybody posts it but I expect this is a very complex query to write manually for anyone without advanced SQL knowledge.</p>
  
  <p>While this isn't an answer that solves the immediate question, I think a fair answer is that this type of query is complex enough that it probably makes sense to build a custom solution (as it sounds like you did) or wait until Sprout Reports (or another alternative exists) that makes it easier to extend the plugin to run more programmatic or memory-intensive queries for use cases like the one you describe.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-15T00:28:41.743Z
Answers.LastActivityDate: 2015-08-15T00:28:41.743Z
Answers.UserDisplayName: Ben Parizek
Body: <p>For the Sprout Reports plugin I need to write a custom SQL query to get some Craft data. (We use this plugin so our client can easily generate CSV exports at any time from the CP).</p>
  
  <p>As there are multiple relations with both Entries and Users, the query becomes quite tricky.. Any help in the right direction would be awesome!</p>
  
  <p>-</p>
  
  <h2>This is the case:</h2>
  
  <p>We have subscriptions, which have subscription data and a relationship to a course (Entry) and a school (User). The course has a relationship to a sportsclub (User). We need a <strong>single</strong> SQL query that pulls all this data from the Craft database.</p>
  
  <p>-</p>
  
  <h2>We have the following tables:</h2>
  
  <p><strong>craft_sections</strong></p>
  
  <pre><code>id|name
  --|-------------
   1|subscriptions
   2|courses
  </code></pre>
  
  <p><strong>craft_entries</strong></p>
  
  <pre><code> id|sectionId
  ---|---------
   50|        1       (subscriptions)
   51|        1       (subscriptions)
   52|        1       (subscriptions)
  100|        2       (courses)
  102|        2       (courses)
  104|        2       (courses)
  </code></pre>
  
  <p><strong>craft_content</strong></p>
  
  <pre><code>id|elementId|name      |gender|city      |course_name     |school_name|club_name
  --|---------|----------|------|----------|----------------|-----------|-----------
   1|       50|John Doe  |Male  |New York  |                |           |
   2|       51|Lisa Smith|Female|New Jersey|                |           |
   3|       52|Brad Bell |Male  |Washington|                |           |   
   4|      100|          |      |          |Basketballcourse|           |
   5|      101|          |      |          |                |School 1   |        
   6|      102|          |      |          |Volleybalcourse |           |
   7|      103|          |      |          |                |School 2   |        
   8|      104|          |      |          |Swimmingcourse  |           |
   9|      105|          |      |          |                |School 3   |
  10|      150|          |      |          |                |           |Sportsclub 1
  11|      151|          |      |          |                |           |Sportsclub 2
  12|      152|          |      |          |                |           |Sportsclub 3
  </code></pre>
  
  <p><em>craft_content elementId = craft_entries id and craft_elements id</em></p>
  
  <p><strong>craft_relations</strong></p>
  
  <pre><code>id|sourceId|targetId
  --|--------|--------
   1|      50|     100       (subscription -&gt; course)
   2|      50|     101       (subscription -&gt; school)
   3|      51|     102       (subscription -&gt; course)
   4|      51|     103       (subscription -&gt; school)
   5|      52|     104       (subscription -&gt; course)
   6|      52|     105       (subscription -&gt; school)
   7|     100|     150       (course -&gt; sportsclub)
   8|     102|     151       (course -&gt; sportsclub)
   9|     104|     152       (course -&gt; sportsclub)
  </code></pre>
  
  <p><em>sourceId and targetId relate to the craft_elements table, where you can get the type of the element. The Ids also relate to the elemendId in the craft_content table.</em></p>
  
  <p><strong>craft_elements</strong></p>
  
  <pre><code>id |type
  ---|-----
   50|Entry       (subscription)
   51|Entry       (subscription)
   52|Entry       (subscription)
  100|Entry       (course)
  101|User        (school)
  102|Entry       (course)
  103|User        (school)
  104|Entry       (course)
  105|User        (school)
  150|User        (sportsclub)
  151|User        (sportsclub)
  152|User        (sportsclub)
  </code></pre>
  
  <p><strong>craft_users</strong></p>
  
  <pre><code>id|username
  --|--------
   1|school_1
   2|school_2
   3|school_3
   4|sportsclub_1
   5|sportsclub_2
   6|sportsclub_3
  </code></pre>
  
  <p><strong>craft_usergroups</strong></p>
  
  <pre><code>id|name
  --|-----------
   1|Schools
   2|Sportsclubs
  </code></pre>
  
  <p><strong>craft_usergroups_users</strong></p>
  
  <pre><code>id|groupId|userId
  --|-------|------
   1|      1|     1
   2|      1|     2
   3|      1|     3
   4|      2|     4
   5|      2|     5
   6|      2|     6
  </code></pre>
  
  <p>-</p>
  
  <h2>The desired result from the query:</h2>
  
  <pre><code>id|name      |gender|city      |course_name     |school_name|club_name
  --|----------|------|----------|----------------|-----------|------------
   1|John Doe  |Male  |New York  |Basketballcourse|School 1   |Sportsclub 1
   2|Lisa Smith|Female|New Jersey|Volleybalcourse |School 2   |Sportsclub 2
   3|Brad Bell |Male  |Washington|Swimmingcourse  |School 3   |Sportclubs 3
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-04-16T12:30:57.583Z
Comments.Text: did you find an answer to this?
Comments.UserDisplayName: buggedcom
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-04-20T09:06:46.990Z
Comments.Text: @buggedcom, I didn't find an answer to this question.. I did manage to fix it in a different way though, by creating a custom export plugin. See: http://craftcms.stackexchange.com/questions/8988/get-related-entry-data-within-a-plugin-elementcriteriamodel-without-extra-quer
  
  This means the single SQL query is possible with joins, but for now I fixed it using the query builder.
Comments.UserDisplayName: 24creative
Tags: <database>
Title: Complex custom SQL query with multiple relationships (Craft DB)
ThreadText: # Complex custom SQL query with multiple relationships (Craft DB)
  
  (Asked by: username_0 on 2015-03-30)
  
  For the Sprout Reports plugin I need to write a custom SQL query to get some Craft data. (We use this plugin so our client can easily generate CSV exports at any time from the CP).
  
  As there are multiple relations with both Entries and Users, the query becomes quite tricky.. Any help in the right direction would be awesome!
  
  -
  
  ## This is the case:
  
  We have subscriptions, which have subscription data and a relationship to a course (Entry) and a school (User). The course has a relationship to a sportsclub (User). We need a **single** SQL query that pulls all this data from the Craft database.
  
  -
  
  ## We have the following tables:
  
  **craft\_sections**
  
  ```
  id|name
  --|-------------
   1|subscriptions
   2|courses
  
  ```
  
  **craft\_entries**
  
  ```
   id|sectionId
  ---|---------
   50|        1       (subscriptions)
   51|        1       (subscriptions)
   52|        1       (subscriptions)
  100|        2       (courses)
  102|        2       (courses)
  104|        2       (courses)
  
  ```
  
  **craft\_content**
  
  ```
  id|elementId|name      |gender|city      |course_name     |school_name|club_name
  --|---------|----------|------|----------|----------------|-----------|-----------
   1|       50|John Doe  |Male  |New York  |                |           |
   2|       51|Lisa Smith|Female|New Jersey|                |           |
   3|       52|Brad Bell |Male  |Washington|                |           |   
   4|      100|          |      |          |Basketballcourse|           |
   5|      101|          |      |          |                |School 1   |        
   6|      102|          |      |          |Volleybalcourse |           |
   7|      103|          |      |          |                |School 2   |        
   8|      104|          |      |          |Swimmingcourse  |           |
   9|      105|          |      |          |                |School 3   |
  10|      150|          |      |          |                |           |Sportsclub 1
  11|      151|          |      |          |                |           |Sportsclub 2
  12|      152|          |      |          |                |           |Sportsclub 3
  
  ```
  
  *craft\_content elementId = craft\_entries id and craft\_elements id*
  
  **craft\_relations**
  
  ```
  id|sourceId|targetId
  --|--------|--------
   1|      50|     100       (subscription -> course)
   2|      50|     101       (subscription -> school)
   3|      51|     102       (subscription -> course)
   4|      51|     103       (subscription -> school)
   5|      52|     104       (subscription -> course)
   6|      52|     105       (subscription -> school)
   7|     100|     150       (course -> sportsclub)
   8|     102|     151       (course -> sportsclub)
   9|     104|     152       (course -> sportsclub)
  
  ```
  
  *sourceId and targetId relate to the craft\_elements table, where you can get the type of the element. The Ids also relate to the elemendId in the craft\_content table.*
  
  **craft\_elements**
  
  ```
  id |type
  ---|-----
   50|Entry       (subscription)
   51|Entry       (subscription)
   52|Entry       (subscription)
  100|Entry       (course)
  101|User        (school)
  102|Entry       (course)
  103|User        (school)
  104|Entry       (course)
  105|User        (school)
  150|User        (sportsclub)
  151|User        (sportsclub)
  152|User        (sportsclub)
  
  ```
  
  **craft\_users**
  
  ```
  id|username
  --|--------
   1|school_1
   2|school_2
   3|school_3
   4|sportsclub_1
   5|sportsclub_2
   6|sportsclub_3
  
  ```
  
  **craft\_usergroups**
  
  ```
  id|name
  --|-----------
   1|Schools
   2|Sportsclubs
  
  ```
  
  **craft\_usergroups\_users**
  
  ```
  id|groupId|userId
  --|-------|------
   1|      1|     1
   2|      1|     2
   3|      1|     3
   4|      2|     4
   5|      2|     5
   6|      2|     6
  
  ```
  
  -
  
  ## The desired result from the query:
  
  ```
  id|name      |gender|city      |course_name     |school_name|club_name
  --|----------|------|----------|----------------|-----------|------------
   1|John Doe  |Male  |New York  |Basketballcourse|School 1   |Sportsclub 1
   2|Lisa Smith|Female|New Jersey|Volleybalcourse |School 2   |Sportsclub 2
   3|Brad Bell |Male  |Washington|Swimmingcourse  |School 3   |Sportclubs 3
  
  ```
  
  **Tags:** database
  
  > **Comments:**
  > **username_1**: did you find an answer to this?  
  > **username_0**: @username_1, I didn't find an answer to this question.. I did manage to fix it in a different way though, by creating a custom export plugin. See: http://craftcms.stackexchange.com/questions/8988/get-related-entry-data-within-a-plugin-elementcriteriamodel-without-extra-quer  This means the single SQL query is possible with joins, but for now I fixed it using the query builder.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2015-08-15. Score: 1
  
  I'll be happy to see the SQL answer if anybody posts it but I expect this is a very complex query to write manually for anyone without advanced SQL knowledge.
  
  While this isn't an answer that solves the immediate question, I think a fair answer is that this type of query is complex enough that it probably makes sense to build a custom solution (as it sounds like you did) or wait until Sprout Reports (or another alternative exists) that makes it easier to extend the plugin to run more programmatic or memory-intensive queries for use cases like the one you describe.
  
  
  ---
  
  