Search code examples
mysqlgoogle-app-maker

Many-to-Many Relation Table Reference in Calculated SQL Query - App Maker

I have two tables, WaterUseData and PermitList with a many-to-many relation that is captured in a table with a name designation of 'WaterUseDataPermits - RelatedPermits' in the default database. I am running a calculated sql datasource against the WaterUseData table where the RelatedPermits key is equal to a parameter of type Permit key that I pass from the client. The relevant query is as follows:

SELECT
  YEAR(Date + INTERVAL 3 MONTH) AS WaterYear,
  SUM(IF(DATE_FORMAT(Date,'%m') = '10',Water_Use_Gallons,null)) as 'Oct',
  SUM(IF(DATE_FORMAT(Date,'%m') = '11',Water_Use_Gallons,null)) as 'Nov',
  SUM(IF(DATE_FORMAT(Date,'%m') = '12',Water_Use_Gallons,null)) as 'Dec',
  SUM(IF(DATE_FORMAT(Date,'%m') = '01',Water_Use_Gallons,null)) as 'Jan',
  SUM(IF(DATE_FORMAT(Date,'%m') = '02',Water_Use_Gallons,null)) as 'Feb',
  SUM(IF(DATE_FORMAT(Date,'%m') = '03',Water_Use_Gallons,null)) as 'Mar',
  SUM(IF(DATE_FORMAT(Date,'%m') = '04',Water_Use_Gallons,null)) as 'Apr',
  SUM(IF(DATE_FORMAT(Date,'%m') = '05',Water_Use_Gallons,null)) as 'May',
  SUM(IF(DATE_FORMAT(Date,'%m') = '06',Water_Use_Gallons,null)) as 'Jun',
  SUM(IF(DATE_FORMAT(Date,'%m') = '07',Water_Use_Gallons,null)) as 'Jul',
  SUM(IF(DATE_FORMAT(Date,'%m') = '08',Water_Use_Gallons,null)) as 'Aug',
  SUM(IF(DATE_FORMAT(Date,'%m') = '09',Water_Use_Gallons,null)) as 'Sep',
  SUM(Water_Use_Gallons) as Total
FROM WaterUseData
WHERE [WaterUseDataPermits - RelatedPermits].RelatedPermits = :PermitKey
GROUP BY YEAR(Date + INTERVAL 3 MONTH)
ORDER BY YEAR(Date + INTERVAL 3 MONTH) ASC

Besides the [WaterUseDataPermits - RelatedPermits] in an attempt to reference the table holding the relation keys I have also tried 'WaterUseDataPermits - RelatedPermits', "WaterUseDataPermits - RelatedPermits", 'WaterUseDataPermits - RelatedPermits', ['WaterUseDataPermits - RelatedPermits'], ["WaterUseDataPermits - RelatedPermits"], and WaterUseDataPermits_RelatedPermits. All of these have resulted in an error in the SQL syntax or an error due to a reference to an unknown column.

Other answers I have found in regards to referencing relation tables have only suggested to include the table name for the relation keys like My_Relation_Table but the table name in App Maker does not appear to conform to this type of naming convention. If anyone might be able to help me solve this problem I would appreciate it.

Added images of of the relevant tables. There are no foreign key fields in either table and the field 'WR_Number' that both tables contain is no longer relevant since App Maker uses a customized relation API.

PermitList Table

WaterUse Table

Solution

  • Based on the useful insight posted by Bruce under the Googe-App-Maker user group here https://groups.google.com/forum/#!topic/appmaker-users/wovN3w3TpT4 I was able to use Calculated SQL models to get a list of all my tables within my database and then I used an additional SQL command to fetch the correct column names within the relation table resulting in an adjusted query that solved my problem. Here are the steps I took to accomplish this:

    1. Created a calculated SQL model, called it 'Tables', created one Field of type string and set the name setting of the field to 'Tables_in_MyDatabaseKey'. Set the datasource query to show tables;. Created a blank page, created a list widget with datasource set to 'Tables', created a label widget within the list row and set binding to @datasource.item.Tables_in_MyDatabaseKey. This resulted in being able to see the correct table name of my relation table.
    2. Created an other calculated SQL model, called it 'TableDescription', created one Field of type string and set the name setting of the field to 'Field'. Set the datasource query to DESCRIBE MyDBTableName; (This portion has been edited to change DESCRIPTION to DESCRIBE, thank you to @Morfinismo for pointing out this error). Added another list widget to my newly created page and set the datasource to 'TableDescription', created a label widget within this list row and set binding to @datasource.item.Field. This displayed the correct column names within my table.

    The corrected SQL query is as follows:

    SELECT
  YEAR(Date + INTERVAL 3 MONTH) AS WaterYear,
  SUM(IF(DATE_FORMAT(Date,'%m') = '10',Water_Use_Gallons,null)) as 'Oct',
  SUM(IF(DATE_FORMAT(Date,'%m') = '11',Water_Use_Gallons,null)) as 'Nov',
  SUM(IF(DATE_FORMAT(Date,'%m') = '12',Water_Use_Gallons,null)) as 'Dec',
  SUM(IF(DATE_FORMAT(Date,'%m') = '01',Water_Use_Gallons,null)) as 'Jan',
  SUM(IF(DATE_FORMAT(Date,'%m') = '02',Water_Use_Gallons,null)) as 'Feb',
  SUM(IF(DATE_FORMAT(Date,'%m') = '03',Water_Use_Gallons,null)) as 'Mar',
  SUM(IF(DATE_FORMAT(Date,'%m') = '04',Water_Use_Gallons,null)) as 'Apr',
  SUM(IF(DATE_FORMAT(Date,'%m') = '05',Water_Use_Gallons,null)) as 'May',
  SUM(IF(DATE_FORMAT(Date,'%m') = '06',Water_Use_Gallons,null)) as 'Jun',
  SUM(IF(DATE_FORMAT(Date,'%m') = '07',Water_Use_Gallons,null)) as 'Jul',
  SUM(IF(DATE_FORMAT(Date,'%m') = '08',Water_Use_Gallons,null)) as 'Aug',
  SUM(IF(DATE_FORMAT(Date,'%m') = '09',Water_Use_Gallons,null)) as 'Sep',
  SUM(Water_Use_Gallons) as Total
FROM WaterUseData, WaterUseDataPermits_HAS_Permits
WHERE WaterUseData.id = WaterUseDataPermits_HAS_Permits.WaterUseDataPermits_fk AND WaterUseDataPermits_HAS_Permits.Permits_fk = :PermitKey
GROUP BY YEAR(Date + INTERVAL 3 MONTH)
ORDER BY YEAR(Date + INTERVAL 3 MONTH) ASC