Query data objectgs

Query data objects in Cloneable by using plain SQL

Writing SQL Queries in Cloneable

This guide explains how to write SQL queries to search and filter your data objects in Cloneable.

The Basics

Your data in Cloneable is stored in two main tables:

• data_objects: Contains your objects

• data_object_fields: Contains the field values for those objects

Writing Simple Queries

Structure of a Basic Query

SELECT do.*
FROM data_objects do
WHERE condition;

Using Dynamic Inputs

Use the @ symbol to create placeholders that you can fill in later:

SELECT do.*
FROM data_objects do
WHERE do.createdAt > @my_date;

Common Examples

1. Find Objects Created After a Specific Date

SELECT do.*
FROM data_objects do
WHERE do.createdAt > @start_date;

2. Find Objects Based on a Field Value

SELECT do.*
FROM data_objects do
INNER JOIN data_object_fields dof ON do.id = dof.instanceObjectId
WHERE dof.name = "temperature"
AND dof.storedValue > @minimum_temp;

3. Find Objects with Specific Status

SELECT do.*
FROM data_objects do
INNER JOIN data_object_fields dof ON do.id = dof.instanceObjectId
WHERE dof.name = "status"
AND dof.storedValue = '"completed"';

Important Naming Rules

1. Dynamic Input Names:

   • Use lowercase letters and underscores: @my_input, @temperature_min

   • Keep names short but descriptive: @start_date, @min_value

   • Examples:

     • Good: @min_temp, @start_date

     • Bad: @MinTemp, @START_DATE

2. Field Names:

   • Must match exactly what's in your object template

   • Case sensitive

   • Examples:

     • If your field is named "temperature", use "temperature" (not "Temperature")

     • If your field is named "transitTime", use "transitTime" (not "transit_time")

Tips for Success

1. Test your queries with simple conditions first

2. Double-check field names - they must match exactly

3. Remember to wrap text values in quotes

4. Use descriptive names for your dynamic inputs with the @ symbol

Examples of Complete Queries

Find High-Value Items

SELECT do.*
FROM data_objects do
INNER JOIN data_object_fields dof ON do.id = dof.instanceObjectId
WHERE dof.name = "price"
AND dof.storedValue > @minimum_price;

Find Recent Active Items

SELECT do.*
FROM data_objects do
INNER JOIN data_object_fields dof ON do.id = dof.instanceObjectId
WHERE dof.name = "status"
AND dof.storedValue = '"active"'
AND do.createdAt > @start_date;

Find Items by Location

SELECT do.*
FROM data_objects do
INNER JOIN data_object_fields dof ON do.id = dof.instanceObjectId
WHERE dof.name = "location"
AND dof.storedValue = '"warehouse_a"';