4

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
Jamie Wade
  • 3,644
  • 2
  • 16
  • 32
24creative
  • 276
  • 1
  • 6
  • did you find an answer to this? – buggedcom Apr 16 '15 at 12:30
  • @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 Apr 20 '15 at 09:06

1 Answers1

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.

Ben Parizek
  • 13,448
  • 1
  • 33
  • 98