How to query a database

Get Help
1

I am struggling to make a script that finds entities unrelated to the current entity/database, so it needs to query the database. It aims to:

  1. Initialize the Fibery Service: Connects to the Fibery API for executing commands and queries.
  2. Query Specific Entities: Fetches entities from the ‘Y CAS/Contact’ database where a specific checkbox field named ‘Select’ is checked.
  3. Update Entities: For each entity retrieved by the query, it attempts to set a single select relationship field named ‘Bucket’ to a Bucket entity that the current entity in context is related to.

I’m not familiar with database queries in Fibery, any guidelines are welcome!

2

For queries, your best bet is probably to use a GraphQl query:

graphql(spacename: string, command: string)

https://the.fibery.io/@public/User_Guide/Guide/GraphQL-Queries-255

Then you can update singularly or in batches:
updateEntity(type: string, id: string, values: object)
updateEntityBatch(type: string, entities: object[])

Use the auto complete to figure out the right syntax :slightly_smiling_face:

1 Like
3

The GraphQl API is definitely easier to figure out and use than the equally-versatile “low level” “executeSingleCommand” interface. And you can use both via curl, etc.

But the GraphQl names of Spaces, DBs and fields are usually different (e.g. non-alphanumeric characters are removed, capitalization is changed).

For this reason I have found it to be a big, error-prone headache to try to mix GraphQl queries with other Fibery APIs (e.g. fibery.getEntityById(), etc.) within automations. :cry:

2 Likes
4

Thank you both!
I got it working in the following way:

For who reads this in the future and wants a working example:

Goal: to automatically update the ‘Bucket’ relationship field of selected contacts in the ‘Contact’ database based on their association with a specific ‘Bucket’ entity defined in the current ‘Y Note’ entity.

Consideration: Using the standard JavaScript API in Fibery is challenging for fetching and linking unrelated entities such as in this case, a task more efficiently handled by GraphQL queries.

const fibery = context.getService('fibery');
const yNoteType = 'Yuri/Y Note'; // Replace with your actual space and type name for Y Note

async function updateContacts() {
    try {
        // Get the current Y Note entity
        const currentEntity = args.currentEntities[0];
        const yNoteEntity = await fibery.getEntityById(yNoteType, currentEntity.id, ['Bucket']);
        const bucketId = yNoteEntity['Bucket'].Id;
        console.log('Bucket Entity ID:', bucketId);

        if (!bucketId) {
            console.log('No Bucket entity found for the Y Note entity.');
            return;
        }

        // GraphQL query to fetch all contacts
        const query = `{
            findContacts {
                id
                select
            }
        }`;

        // Execute the GraphQL query
        const response = await fibery.graphql('Y CAS', query);
        console.log('GraphQL Response:', response);

        const selectedContacts = response.data.findContacts.filter(contact => contact.select);
        console.log('Selected Contacts:', selectedContacts);

        if (selectedContacts.length === 0) {
            console.log('No selected contacts found.');
            return;
        }

        // Update each contact's 'Bucket' field
        for (const contact of selectedContacts) {
            await fibery.updateEntity('Y CAS/Contact', contact.id, { 'Bucket': bucketId });
            console.log('Updated Contact ID:', contact.id);
        }

        console.log('All selected Contacts updated with Bucket.');
    } catch (error) {
        console.error('Error in script:', error);
    }
}

await updateContacts();

Explanation

  1. Define GraphQL Query:

     const query = `{
     findContacts {
         id
         select
     }
 }`;

    The script defines a GraphQL query to fetch all contacts from the ‘Y CAS/Contact’ database. It retrieves each contact’s ID and the state of their ‘select’ checkbox field.

  2. Execute GraphQL Query:
    const response = await fibery.graphql('Y CAS', query);
    The script executes the GraphQL query within the ‘Y CAS’ space in Fibery, gathering data about all contacts.

  3. Filter Selected Contacts:

     const selectedContacts = response.data.findContacts.filter(contact => contact.select);

    The script processes the query response to filter out only those contacts where the ‘select’ field is true, indicating they are the ones to be updated.

This part of the script identifies which contacts need their ‘Bucket’ field updated, based on the selection criteria.

1 Like
5

Fwiw, you can use filtering directly in graphql queries, which would save you a step.

1 Like