Alpha DevCon 2018
Results 1 to 7 of 7

Thread: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

  1. #1
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,301

    Default List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    Hi everyone,

    I'm looking into taking advantage of the nested JSON Array syntax to display "joined" tables of records so I can guarantee that all of my, say, employees are shown with their respective company in a List control that is paginated. These companies have a relatively low number of employees each (usually 1 or 0, but as many as 5.)

    I looked at just using a SQL query with a join and Group Breaks and an epic number of columns. However, in the case of the lonely corporation with no one to work for it, I was getting blank rows and that's no good.

    I also liked the idea of having a simple List Template and having fancy arrays as members of each record.

    Does anyone have any advice on how best to query the db and put the data into this format:

    Code:
    [
      {
          COMPANY_ID : 1,
          COMPANY_NAME : "Things & Stuff",
          EMPLOYEES : [
                         { NAME : "Some Guy" },
                         { NAME : "John Doe"}
                      ]
       },
       {
          COMPANY_ID : 2,
          COMPANY_NAME : "Lonely Co",
          EMPLOYEES : []
       }
    ]
    I'm guessing that I'll have to do two SQL queries - one Query to get the top level records. A second to pull all the children. I was looking at using toPropertyArray method of the SQL::Connection object and then looping through all the parent & child records to merge them. If anyone has a better solution/different solution, I'd love to hear it.

    Thanks!
    Alpha Five Version 11 Build 3381-4096
    Alpha Anywhere Version 12.3 Build 2614-4409
    Find me on Github!

  2. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    6,669

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    Hey Sarah... it's a little hard for me to fathom what you're looking for. I understand parts of it... but can't get a good picture. Is it something like this?
    CompEmp.PNG

  3. #3
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,301

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    Quote Originally Posted by Davidk View Post
    Hey Sarah... it's a little hard for me to fathom what you're looking for. I understand parts of it... but can't get a good picture. Is it something like this?
    CompEmp.PNG
    That's the results I had originally. I had a SQL query, but each row as an "employee". Except, in the case of Lonely Co, there were no employees, so I was getting blank lines.

    So I thought a better way to approach this so that (1) pagination was 20 *companies*, not 20 employees and (2) eliminated blank lines was to get my SQL data into the form below:
    Code:
    [
      {
          COMPANY_ID : 1,
          COMPANY_NAME : "Things & Stuff",
          EMPLOYEES : [
                         { NAME : "Some Guy", POSITION: "Manager" },
                         { NAME : "John Doe", POSITION: "Clerk"}
                      ]
       },
       {
          COMPANY_ID : 2,
          COMPANY_NAME : "Lonely Co",
          EMPLOYEES : []
       },
       {
          COMPANY_ID : 3,
          COMPANY_NAME : "Mart's Walls",
          EMPLOYEES : [
                         { NAME : "Janice", POSITION : "Site Manager" },
                         { NAME : "Mauriece", POSITION : "Cashier"},
                         { NAME : "Fred", POSITION : "Cashier"}
                      ]
       },
       {
          COMPANY_ID : 4,
          COMPANY_NAME : "HasBros",
          EMPLOYEES : [
                         { NAME : "Sampsonite Jones", POSITION : "Janitor" }
                      ]
       }
    ]
    So I could use this template instead:
    Code:
    <div style="background-color:#efefef;font-size:18px;font-weight:bold;padding-left:10px;border-bottom:1px solid #808080;">{COMPANY_NAME}</div>
    <div>{EMPLOYEES}
      {*empty}
      {/*empty}
      {*header}
    <div style="font-weight:bold;font-size:14px;margin-bottom:5px;border-bottom:1px dotted;"><span style="width:250px;padding-left:10px;display:inline-block;">Employees</span><span style="width:300px;padding-left:10px;display:inline-block;">Position</span></div>
      {/*header}
    <div><span style="width:250px;padding-left:10px;display:inline-block;">{NAME}</span><span style="width:300px;padding-left:10px;display:inline-block;">{POSITION}</span></div>
    {/EMPLOYEES}</div>
    My list looks more like this now:
    listLayoutPreview.png

    My question's pertains to getting the data from the SQL database and formatting it into JSON with Employee records as arrays - Are there any suggestions on how best to approach this? Here's what I came up with, but I want to know if someone knows of a better way to do this:
    Code:
    	sql1 =<<%sql1%
    SELECT *
    FROM company
    ORDER BY ID DESC
    %sql1%
    
    	sql2 =<<%sql2%
    SELECT * 
    FROM emp_to_company
    	 LEFT OUTER JOIN emp ON emp_to_company.EMP_ID = emp.ID
    ORDER BY emp_to_company.COMPANY_ID DESC
    %sql2%
    
    dim cn as sql::Connection
    if (cn.open("::Name::conn") <> .t.) then
     'error
     end
    end if
    
    dim company[0] as p
    dim employee[0] as p
    
    dim args as sql::Arguments
    
    if (cn.toPropertyArray(sql1,args,company) <> .t.) then
      'error
      cn.close()
      end
    end if
    
    if (cn.toPropertyArray(sql2,args,employee) <> .t.) then
      'error
      cn.close()
      end
    end if
    
    cn.close()
    
    dim i as n = 0
    dim j as n = 1
    for i = 1 to company.size()
      ' because data is sorted
      while (j <= employee.size() .and. employee[j].EMP_ID == company[i].ID)
        *array_append(company[i].EMPLOYEE, employee[j])
        j = j + 1
      end while
    next i
    
    json_result = json_generate(company,.f.,.t.,"",.f.)
    Last edited by TheSmitchell; 07-02-2015 at 10:07 AM.
    Alpha Five Version 11 Build 3381-4096
    Alpha Anywhere Version 12.3 Build 2614-4409
    Find me on Github!

  4. #4
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    6,669

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    I'd say what you've got is excellent. You want a very specific output and achieved that with very little code and are making great use of the List control options for getting exactly what you need.

    If the List control had a "suppress row if empty" option that might do it... but given the List control's flexibility... you've tied it in really well.

  5. #5
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,301

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    Quote Originally Posted by Davidk View Post
    I'd say what you've got is excellent. You want a very specific output and achieved that with very little code and are making great use of the List control options for getting exactly what you need.

    If the List control had a "suppress row if empty" option that might do it... but given the List control's flexibility... you've tied it in really well.
    Ha ha! Well, it looks like the List Control doesn't exactly handle my case very well. It doesn't show any of the Columns! EEK. Hope I don't need to set any properties on these things.
    Alpha Five Version 11 Build 3381-4096
    Alpha Anywhere Version 12.3 Build 2614-4409
    Find me on Github!

  6. #6
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,301

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    HA!

    I was having trouble getting an empty list into the JSON object returned from my function. I found that I had to run my generated JSON through json_standardize before returning it.

    Step 1: Get Parent Records

    This was the first thing that had to happen. I used cn.toPropertyArray to get my parent records:
    Code:
    dim groups[0] as p
    dim numToGet as n = -1
    dim startRec as n = -1
    
    'Handles pagination if you have it:
    if (e.getDataMode = "Navigate" .or. e.getDataMode = "fetchMore") then
    	numToGet = e.pageSize
    	startRec = e.targetLogicalRecordNumber
    end if
    
    flag = cn.toPropertyArray(sql,args,groups,numToGet,startRec)
    
    if flag = .f. then 
    	e.fatalError = .t.
    	e.errorText = "Could not execute query. " + crlf(2) + "Error reported was: " + cn.CallResult.text 
    	cn.close()
    	exit function 
    end if
    Step 2: Get Child Records
    Next are the child records I wanted. I generated a list of all the parent records so I could fetch only the child records I needed and put the children into a property array0:

    Code:
    dim members[0] as p
    
    if (e.paginateData == .t.) then
            'Actually only need to do this if we're paginating. 
            'If not, then groups will contain all of the parent records, doing this step is pointless
            'and chews up cycles
    	dim groupIds[0] as n
    	for i = 1 to groups.size()
    		*array_append(groupIds,groups[i].GROUP_ID)
    	next i
    
    	args.set("GROUPS",crlf_to_comma(groupIds.dump()))
    end if
    
    flag = cn.toPropertyArray(sql_children,args,members)
    
    if flag = .f. then 
    	e.fatalError = .t.
    	e.errorText = "Could not execute query. " + crlf(2) + "Error reported was: " + cn.CallResult.text 
    	cn.close()
    	exit function 
    end if
    Step 3: Adding Children to Parent Records

    After I had all my records, I looped through the children and parent records and mashed everything together:
    Code:
    dim nullArr[0] as p
    dim j as n = 1
    for i = 1 to groups.size()
      while (j <= members.size() .and. members[j].GROUP_ID == groups[i].GROUP_ID) 
      	*array_append(groups[i].PEOPLE,members[j])
      	j = j + 1
      end while
      
      'If no People records were created, add PEOPLE as an *empty array* to groups[i]
      if (variable_exists("groups[i].PEOPLE") <> .t.) then
      	groups[i].PEOPLE = nullArr
      end if
    next i
    Step 4: Generating JSON

    This is the part that will result in hair loss. For the parent records that *had no children*, Alpha was barfing all over the json I was returning because it was apparently in a NON-STANDARD format. Jubilation. (I spent 8 hours trying to figure this out only to find that I just need a single function call to json_standardize.)
    Code:
    txt = json_generate(groups,.f.,.f.,"",.f.)
    txt = json_standardize(txt)
    AND NOW I CAN USE {PEOPLE}{*empty}{/*empty}{/PEOPLE} IN MY LIST TEMPALTE AND THE LIST DOESN'T EXPLODE WHEN THE FIRST RECORD HAS NO CHILD PEOPLE RECORDS.
    Alpha Five Version 11 Build 3381-4096
    Alpha Anywhere Version 12.3 Build 2614-4409
    Find me on Github!

  7. #7
    Member
    Real Name
    Michael Cornford
    Join Date
    Oct 2011
    Location
    East Sussex, UK
    Posts
    884

    Default Re: List w/ Custom Data Source - SQL Query with Nested JSON Arrays

    Lost my place but sent an email.

    Here is the output directly from MySQL not sure if I have missed or added things (, or brackets) but it is simple to change.

    [
    {
    Company_Id: 1,
    Company_Name: Alpha,
    Employee: [
    {Name: Alex Alpha , Age: 23} ,
    {Name: Bert Alpha , Age: 32} ,
    {Name: Colin Alpha , Age: 60}]},

    {
    Company_Id: 2,
    Company_Name: Beta,
    Employee: []},

    {
    Company_Id: 3,
    Company_Name: Gamma,
    Employee: [
    {Name: Andy Gamma , Age: 45}]},

    {
    Company_Id: 4,
    Company_Name: Delta,
    Employee: [
    {Name: Ann Delta , Age: 18} ,
    {Name: Brian Delta , Age: 55}]

    }
    ]

    The output comes from 2 tables.

    I wrote 5 views for clarity. it builds this in 3/1000 of a second but I guess you could speed it up if you have a big data set.


    Michael

Similar Threads

  1. List control with custom data source and parent list
    By sjackson@drake in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 07-01-2015, 09:26 PM
  2. Sharepoint list as data source
    By John_Titor in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 06-24-2015, 08:39 AM
  3. Populate List through custom data source - Help
    By mikeallenbrown in forum Mobile & Browser Applications
    Replies: 4
    Last Post: 05-27-2015, 10:08 AM
  4. Need help wth JSON List SQL Update
    By Tbrondolo in forum Mobile & Browser Applications
    Replies: 6
    Last Post: 07-30-2014, 06:24 PM
  5. Replies: 1
    Last Post: 11-26-2013, 02:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •