C3 AI Documentation Home

Fetch Data From Reference and Collection Fields Using Python

Once you relate two Entity Types using a reference field, you are ready to persist relational data for later retrieval. This also means your data spans several Types, so when fetching data you need to specify which Types to fetch from.

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 that 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 that spans multiple Types or computing values in real time. Those fetch those fields, you need to mention them explicitly in your query.

Data modeling

Let's assume you've defined two Types to keep track of buildings and smart bulbs installed in those buildings. The definition of smart bulb looks like this:

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

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

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

And the definition of building looks like:

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

    bulbs: [SmartBulb](building)
}

Also, let's also assume you've added some data to the database:

Python
# Create multiple smart bulbs in-memory
bulbs = [
    {"id": "oBx8wE", "bulbType": "LED", "manufacturer": "Philips", "wattage": 17, "lumens": 1200, "building": "FEV8Mv"},
    {"id": "plMFdy", "bulbType": "LED", "manufacturer": "Philips", "wattage": 14, "lumens": 1150, "building": "FEV8Mv"},
    {"id": "Ro3mFX", "bulbType": "CFL", "manufacturer": "Philips", "wattage": 23, "lumens": 1370, "building": "3pq3if"},
    {"id": "nSQSjK", "bulbType": "CFL", "manufacturer": "Philips", "wattage": 23, "lumens": 1430, "building": "3pq3if"},
    {"id": "G4lnkc", "bulbType": "LED", "manufacturer": "GE",      "wattage": 11, "lumens": 1100, "building": "FEV8Mv"},
    {"id": "q8Syds", "bulbType": "LED", "manufacturer": "GE",      "wattage": 10, "lumens": 1000, "building": "FEV8Mv"}
]

# Create multiple buildings in memory
buildings = [
    {"id": "FEV8Mv", "address": "1300 Seaport Blvd 500 Floor 1, Floor 1" },
    {"id": "3pq3if", "address": "1300 Seaport Blvd 500, Floor 2" },
    {"id": "Gt8fJP", "address": "1300 Seaport Blvd 500, Rooftop" }
]

# Persist bulbs and buildings in the database:
c3.SmartBulb.createBatch(bulbs)
c3.Building.createBatch(buildings)

Fetch all persisted fields

By default, if you don't specify any fields, only fields with primitive data types and stored calculated fields are fetched:

Python
c3.SmartBulb.fetch()

# Same as
c3.SmartBulb.fetch(spec={"include": "[this]"})

Fetch specific fields

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

Python
# Include the list of fields to fetch
c3.SmartBulb.fetch({"include": "[id, manufacturer, lumensPerWatt]"})

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:

Python
# Similar to
# 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'
c3.SmartBulb.fetch({
    "include": "[id, manufacturer, building.id, building.address]",
    "filter": "endsWith(building.address, 'Floor 1')"
})

# Similar to
# SELECT SmartBulb.*, Building.*
# FROM SmartBulb LEFT JOIN Building ON SmartBulb.building = Building.id
# WHERE building.address LIKE '%Floor 1'
c3.SmartBulb.fetch({
    "include": "[this, building.this]",
    "filter": "endsWith(building.address, '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.

Fetch and filter collection fields

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

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

Again, 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.

Fetch only instances with a relationship

As you noticed, 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:

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

See also

Was this page helpful?