Fetch Data from Reference and Collection Fields Using JavaScript

You can use the Type keyword entity to persist data on disk, and you can also relate two Entity Types using a reference field. Relating two types using a reference field also means your data spans several Types, so when fetching data you need to specify from which Types to fetch.

To ensure your queries are performant, by default the Persistable#fetch method only fetches data for fields that are directly stored in the underlying database table associated with the Entity Type. This means only fields with a primitive Type and stored calculated fields are fetched.

Collections and non-stored calculated fields are not fetched by default since that requires fetching data spanning multiple Types or computing values in real time. To fetch those fields, you must use the fields explicitly in your query.

Copy link to this section

Data modeling

Extending the SmartBulb Type example from Fetching and Filtering Data from the Database using JavaScript add a new field called building that represents a Building Type:

Clear any persisted content from the SmartBulb Type by running:

SmartBulb.removeAll(null, true);

Update the SmartBulb Type by adding a building field that references a Building Type:

/* SmartBulb.c3typ */
entity type SmartBulb schema name "SMRT_BLB" {
    bulbType: !string enum('LED', 'INCAN', 'CFL')
    manufacturer: string
    wattage: decimal
    lumensUOM: decimal

    // In what building this smart bulb is installed
    building: Building

    // Calculated field
    lumensPerWatt: decimal calc "lumensUOM / wattage"
}

Define the Building Type:

/* Building.c3typ */
entity type Building schema name "BLDG" {
    address: !string

    bulbs: [SmartBulb](building)
}

You now have two Entity Types to keep track of buildings and the smart bulbs installed in those buildings.

Add data to the database:

// Create multiple smart bulbs in-memory
var bulbs = [
    {id: "oBx8wE", bulbType: "LED", manufacturer: "Philips", wattage: 17, lumensUOM: 1200, building: "FEV8Mv"},
    {id: "plMFdy", bulbType: "LED", manufacturer: "Philips", wattage: 14, lumensUOM: 1150, building: "FEV8Mv"},
    {id: "Ro3mFX", bulbType: "CFL", manufacturer: "Philips", wattage: 23, lumensUOM: 1370, building: "3pq3if"},
    {id: "nSQSjK", bulbType: "CFL", manufacturer: "Philips", wattage: 23, lumensUOM: 1430, building: "3pq3if"},
    {id: "G4lnkc", bulbType: "LED", manufacturer: "GE",      wattage: 11, lumensUOM: 1100, building: "FEV8Mv"},
    {id: "q8Syds", bulbType: "LED", manufacturer: "GE",      wattage: 10, lumensUOM: 3400, building: "Gt8fJP"},
    {id: "sERGTw", bulbType: "CFL", manufacturer: "Philips", wattage: 23, lumensUOM: 1430, building: "3pq3if"},
    {id: "Sffsds", bulbType: "LED", manufacturer: "GE",      wattage: 11, lumensUOM: 1100, building: "FEV8Mv"},
    {id: "w4rFsd", bulbType: "LED", manufacturer: "GE",      wattage: 10, lumensUOM: 1000, building: "FEV8Mv"}
];

var buildings = [
    {id: "FEV8Mv", address: "1400 Seaport Blvd 500 Floor 1, Floor 1" },
    {id: "3pq3if", address: "1400 Seaport Blvd 500, Floor 2" },
    {id: "Gt8fJP", address: "1400 Seaport Blvd 500, Rooftop" }
];

// Persist bulbs and buildings in the database:
SmartBulb.createBatch(bulbs);
Building.createBatch(buildings);

Copy link to this section

Fetch all persisted fields

By default, if you do not specify any fields, only fields with primitive data types and stored calculated fields are fetched:

SmartBulb.fetch();

// Same as
SmartBulb.fetch({
   include: "[this]"
});

this is a reference variable that refers to the current instance of the object itself.

Copy link to this section

Fetch specific fields

To specify which fields to fetch, use the include parameter:

// Include the list of fields to fetch
SmartBulb.fetch({
    include: "[id, manufacturer, lumensUOM]"
});

The next query returns all Building Types in the database and only includes the id field in the returned objects. The address field on the Building Type is not be included.

Building.fetch({
    include : "[id]"
}).objs

Copy link to this section

Fetch and filter reference fields

When a Type references another, you can fetch information for both of them by listing which fields of the referenced Type you want to fetch:

SmartBulb.fetch({
    include: "[id, manufacturer, building.id, building.address]",
    filter: "endsWith(building.address, 'Floor 1')"
});

The above fetch is similar to a SQL query retrieving information from a database involving two tables: SmartBulb and Building.

SELECT SmartBulb.id, SmartBulb.manufacturer, Building.id, Building.address
FROM SmartBulb LEFT JOIN Building ON SmartBulb.building = Building.id
WHERE building.address LIKE '%Floor 1'

The components of the query are:

• SELECT Clause

  • SmartBulb.id — This is selecting the id column from the SmartBulb table.
  • SmartBulb.manufacturer — This is selecting the manufacturer column from the SmartBulb table.
  • Building.id — This is selecting the id column from the Building table.
  • Building.address — This is selecting the address column from the Building table.

• FROM Clause

  • The query is pulling from the SmartBulb table.

• LEFT JOIN

  • The LEFT JOIN keyword returns all records from the left table (SmartBulb), and the matched records from the right table (Building). The result is NULL from the right side, if there is no match.
  • The join is based on the condition specified in an ON clause: SmartBulb.building = Building.id. There is a field named building in the SmartBulb table that stores the id of the associated building from the Building table.

• WHERE Clause

  • The condition is filtering the results to only include records where the address column in the Building table contains the text "Floor 1". The % characters are wildcards in SQL that match any number of characters, so this condition would match addresses like "123 Main St, Floor 1", "Floor 1, Room 5", etc.

The query is retrieving the IDs and manufacturers of smart bulbs, and the IDs and addresses of the buildings they are in, specifically for those buildings whose address includes the text "Floor 1".

SmartBulb.fetch({
    include: "[this, building.this]",
    filter: "endsWith(building.address, 'Floor 1')"
});

SELECT SmartBulb.*, Building.*
FROM SmartBulb LEFT JOIN Building ON SmartBulb.building = Building.id
WHERE building.address LIKE '%Floor 1'

The data model in this example is simple, but you can include as many reference fields in your query to fetch the information you need.

Copy link to this section

Fetch and filter collection fields

Both collection and reference fields are not fetched by default. You need to list them explicitly in your query:

// Similar to
// SELECT Building.address, SmartBulb.manufacturer, (SmartBulb.lumensUOM / SmartBulb.wattage)
// FROM Building LEFT JOIN SmartBulb ON Building.id = SmartBulb.building
// WHERE SmartBulb.bulbType = 'LED'
Building.fetch({
    include: "[address, bulbs.manufacturer, bulbs.lumensPerWatt]",
    filter: "bulbs.bulbType == 'LED'"
});

The data model in this example is simple, but you can include as many collection fields in your query to fetch the information you need.

You can also include both reference and collection fields in your query to be able to fetch data between any two Types that have a relationship.

Copy link to this section

Fetch only instances with a relationship

It is worth noting, Building.fetch({include: [address, bulbs.this]}) fetches all buildings, no matter if they have smart bulbs installed or not. To fetch only buildings that have at least one smart bulb associated, use the filter parameter:

// Similar to an inner join
Building.fetch({
    include: "[address, bulbs.this]",
    filter: "exists(bulbs.id)"
})

The include field offers support for querying using the dot syntax notation.

Building.fetch({
    include : "[address, windTurbines.(manufacturer=='GE')]",
    filter : "windTurbines.manufacturer == 'GE'"
})

Copy link to this section

See also

• Persistable
• Relate Two Entity Types Using a Reference Field
• Persist and Update Data
• Fetch and query a single Type