[SQL] Best way to create a vertex and a multitudes of edges (coming out of this newly created vertex)

[ExtReMLapin]

Hello,
As we're migrating some of our Cypher queries to SQL, I lack the experience with ArcadeDB SQL;

From my understanding, SQLScript is a good way to reduce overhead caused by a crazy amount of small REST API Calls.

The same way we can use UNWIND with Cypher, we can use it with SQL but we can also use scripts.

The idea is that we create a vertex and it will already have relations/edges.

What we did before with cypher is creating it the Vertex/Node with one query, and all the relations in a batch_request, we ported this code to SQL and it works great so far.

    def create_relations_batched(self, case_id: str, source_type:NodesTypes, source_ids: list[str], target_type:NodesTypes, target_ids: list[str], relation_type: str, relation_features: list[dict], language:str="sql"):
        assert case_id in self.databases, f"Database/Case ID {case_id} doesn't exist."
        if len(source_ids) == 0 or len(target_ids) == 0:
            return []
        
        selected_database = self.databases[case_id]


        # Normalize the source_ids and target_ids for the modes (One-to-Many, Many-to-One)
        if len(source_ids) == 1 and len(target_ids) > 1:
            source_ids = source_ids * len(target_ids)  # ONE_TO_MANY mode
        elif len(target_ids) == 1 and len(source_ids) > 1:
            target_ids = target_ids * len(source_ids)  # MANY_TO_ONE mode

        assert len(source_ids) == len(target_ids), f"source_ids and target_ids must be the same length after normalization, got {len(source_ids)} and {len(target_ids)}"

        # Prepare the list of relations to be created
        batch_data = []
        for i in range(len(source_ids)):
            feature_data = {key: value for key, value in relation_features[i].items() if key not in ['source_id', 'target_id']}
            batch_data.append({
                "source_id": source_ids[i],
                "target_id": target_ids[i],
                "features": feature_data
            })
        
        if language == "cypher":
            query = f"""
            UNWIND $batch as row
            MATCH (a:{source_type.value}) WHERE ID(a) = row.source_id
            MATCH (b:{target_type.value}) WHERE ID(b) = row.target_id
            MERGE (a)-[r:{relation_type}]->(b)
            """

            # Dynamically add relation features in the SET clause
            if any(relation_features):
                set_clauses = []
                for key in relation_features[0].keys():
                    set_clauses.append(f"r.{key} = row.features.{key}")
                set_clause = "SET " + ", ".join(set_clauses)
                query += set_clause

            query += " RETURN a, b, r"
            # Prepare the data for batch processing
            # Execute the batched query
            res = selected_database.query('cypher', query, params={'batch': batch_data})
        elif language == "sql":
            query = f"""
            CREATE EDGE TYPE {relation_type} IF NOT EXISTS;
            """

            for i, row in enumerate(batch_data):
                source_id = row['source_id']
                target_id = row['target_id']
                features = row['features']
                query += f"CREATE EDGE {relation_type} FROM {source_id} TO {target_id}"
                if any(features):
                    feature_array = []
                    for key, value in features.items():
                        if type(value) not in self.types_dont_escape:
                            feature_array.append(f"{key} = :{key}_{i}")
                        else:
                            feature_array.append(f"{key} = {self.convert_type(value)}")
                    feature_string = ", ".join(feature_array)
                    query += f" SET {feature_string}"
                query += ";"
            res = selected_database.query('sqlscript', query, params={f"{key}_{i}": value for i, row in enumerate(batch_data) for key, value in row['features'].items()}, is_command=True)
        else:
            raise ValueError("language must be 'cypher' or 'sql'")

        return res

Now, what if we want to -in the same request- create the vertex/node and edges coming out of this vertex ?

The destination vertex can be created in the same query or already exist.

Is it the best way to do ?

create vertex type `CHARACTER` IF NOT EXISTS;
LET $edgeA = INSERT INTO `CHARACTER` SET name = 'Homer Simpson'  RETURN  @rid;
LET $edgeB = INSERT INTO `CHARACTER` SET name = 'Ned Flanders'  RETURN  @rid;
CREATE EDGE TYPE `Neighbors` IF NOT EXISTS;
CREATE EDGE `Neighbors` FROM  $edgeA.@rid TO $edgeB.@rid;

The very first line will be omited in the final code, CREATE EDGE TYPE will not be, as it's a LLM that decides the edges names.