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

CC BY-SA 3.0
2015-08-15T00:28:41.743Z
2015-08-15T00:28:41.743Z
Ben Parizek
<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>

CC BY-SA 3.0
2015-04-16T12:30:57.583Z
did you find an answer to this?
buggedcom
CC BY-SA 3.0
2015-04-20T09:06:46.990Z
@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.
24creative
<database>
Complex custom SQL query with multiple relationships (Craft DB)
# 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.


---

