Complex custom SQL query with multiple relationships (Craft DB) 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 -&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)


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