def descendant_relationships(codes):
codes = list(codes)
placeholders = ", ".join(["%s"] * len(codes))
sql = f"""
WITH RECURSIVE tree(parent_id, child_id) AS (
SELECT
destination_id AS parent_id,
source_id AS child_id
FROM snomedct_relationship
WHERE parent_id IN ({placeholders})
AND type_id = '{IS_A}'
AND active
UNION
SELECT
r.destination_id AS parent_id,
r.source_id AS child_id
FROM snomedct_relationship r
INNER JOIN tree t
ON r.destination_id = t.child_id
WHERE r.type_id = '{IS_A}'
AND active
)
SELECT parent_id, child_id FROM tree
"""
return query(sql, codes)
def test_query_with_many_params(self):
values = list(range(5000))
placeholders = ["%s"] * len(values)
sql = "SELECT 'found' WHERE %s IN ({})".format(", ".join(placeholders))
last_value = values[-1]
params = [last_value] + values
result = db_utils.query(sql, params)
self.assertEqual(result, [("found", )])
def descendant_relationships(codes):
placeholders = ", ".join(["%s"] * len(codes))
sql = f"""
WITH RECURSIVE tree(parent_id, child_id) AS (
SELECT parent_id, child_id
FROM ctv3_concepthierarchy
WHERE parent_id IN ({placeholders})
UNION
SELECT h.parent_id, h.child_id
FROM ctv3_concepthierarchy h
INNER JOIN tree t
ON h.parent_id = t.child_id
)
SELECT parent_id, child_id FROM tree
"""
return query(sql, codes)
def descendant_relationships(codes):
codes = list(codes)
concept_table = Concept._meta.db_table
placeholders = ", ".join(["%s"] * len(codes))
sql = f"""
WITH RECURSIVE tree(parent_code, child_code) AS (
SELECT parent_id AS parent_code, code AS child_code
FROM {concept_table}
WHERE parent_code IN ({placeholders})
UNION
SELECT c.parent_id AS parent_code, c.code AS child_code
FROM {concept_table} c
INNER JOIN tree t
ON c.parent_id = t.child_code
)
SELECT parent_code, child_code FROM tree
"""
return query(sql, codes)
def descendant_relationships(codes):
codes = list(codes)
relationship_table = TPPRelationship._meta.db_table
placeholders = ", ".join(["%s"] * len(codes))
sql = f"""
WITH RECURSIVE tree(ancestor_id, descendant_id) AS (
SELECT ancestor_id, descendant_id
FROM {relationship_table}
WHERE ancestor_id IN ({placeholders}) AND distance = 1
UNION
SELECT r.ancestor_id, r.descendant_id
FROM {relationship_table} r
INNER JOIN tree t
ON r.ancestor_id = t.descendant_id
WHERE distance = 1
)
SELECT ancestor_id, descendant_id FROM tree
"""
return query(sql, codes)
