Re: Multi-Table Query Into JSON?

[Kelderic via 4D_Tech]

I ended up writing a wrapper method called SelectionToJSON (named to match
our existing naming convention), which is called like this:

SelectionToJSON("Table";"[Table]Field1;[Table]Field2;[RelatedTable]Field1")

Inside the method, it breaks apart the second parameter and builds a
template object. If the field is not part of the main method, it prepends
"TableName_" to the JSON key.

It does rely on having the automatic relations set up in the structure,
which I had to play around with to get working. I ended up having to call
Set Automation Relations(True;True) at the beginning of the method.

As to whether Selection to JSON should be used in production, it seems to me
like it's meant to be used because it's a LOT faster than looping through
arrays and building JSON strings manually, which is what we used to do. I've
cut 30 second response times down to 10, for very large lists.



--
Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Multi-Table Query Into JSON?

[Kelderic via 4D_Tech]

I have a database with tables that are linked together via IDs. Simplified
example:

CallLogs Table
UniqueID, Date, Type, Log, CustomerID

Customers Table
UniqueID, FirstName, LastName, City, State

A CallLogs record will be linked to a Customers record by:

[CallLogs]CustomerID = [Customers]UniqueID



All of that is working. I have been using Selection to JSON to send data
from the DB out to a web app. However, the list of CallLogs doesn't have the
name of the linked Customer, because Selection to JSON only works with one
table.

I want to be able to return a joined set of data.

With SQL, I can do a JOIN request into an array of objects, and then convert
that array to JSON to send out. However, I can't figure out how to get a
joined set of data out of 4D. I can't even get multiple fields in ONE table,
into an object array.

Here is what I have tried:



ARRAY OBJECT(aoRecords;0)

Begin SQL
SELECT *
FROM CallLogs
INTO :aoRecords
End SQL



Is there a way to accomplish this besides querying and then looping through
and building the arrays myself? Thanks!




--
Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**