MarySC good day 🙂
import sql_query
import catalogs
(Create a list of fields we want to retrieve)
fields = ("Id", "Email", "Department")
(List of tuples with model fields from components we are joining)
fields_join = (("Id", "DepartmentName"), ("Id", "ProjectName"))
(GUID of the main component)
component = 'cee394d2-64fd-49d0-9221-1068ae010495'
(Specify which components to join and which fields to compare)
join_on = {'123123-456456-456-9221-100005': ('Department', 'Id'), '83e6ce3f-1211-4183-905c-29a3e1dcf468': ('Project', 'Id')}
(Dictionary of fields and their values for filtering using WHERE)
filter_fields = {'Email': "email", 'Id': 1, 'IsExist': (True,)}
(Get the SQL query as a string)
query_str = sql_query.filter_join_query(fields, component, fields_join, join_on, filter_fields)
(Create the query)
query = catalogs.query(query_str)
(Execute the query)
item["query_result"] = query.execute()
(Result)
"""
SELECT cmp."Id" as "Id",
cmp."Data_Email" as "Email",
cmp."Data_Department" as "Department",
jn1."Id" as "jn1_Id",
jn1."Data_DepartmentName" as "DepartmentName",
jn2."Id" as "jn2_Id",
jn2."Data_ProjectName" as "ProjectName"
FROM "cee394d2-64fd-49d0-9221-1068ae010495" as cmp
INNER JOIN "123123-456456-456-9221-100005" as jn1 ON cmp."Data_Department" = jn1."Id"
INNER JOIN "83e6ce3f-1211-4183-905c-29a3e1dcf468" as jn2 ON cmp."Data_Project" = jn2."Id"
WHERE cmp."Data_Email" = 'email' AND cmp."Id" = 1 AND cmp."Data_IsExist" <> true
"""