05-29-2020, 03:59 PM
(This post was last modified: 05-29-2020, 04:00 PM by remkonoteboom.)
This line:
<connect from="dolly3d/assets" to="dolly3d/assets" instance_type="dolly3d/assets_in_assets" relationship="instance"/>
is problematic. The schema I have been using for testing is this:
Note that the instance connection has both to and from search types the same. This is problematic. The instance relationship was originally designed to work with one table to another different table with an "instance" table in between. When the "to" and the "from" tables are the same, do something like:
gives an SQL error complaining about referencing the "asset" table twice. This makes sense because the instance relationship never took this situation into account and some work needs to be done to get this to work. I tweaked the output SQL and it *should* look like this:
This would produce the correct result. Again this is going from the parent to children so we'd have to indicate a direction to go the other way around. It all gets very complicated using instances with tables to itself.
Expressions such as these do seem to work:
This is explicit and clear which direction you are going, however, it's a bit tedious, but I think necessary to indicate how the flow goes until we have a better "instance" implementation for tables reference itself.
This is all a summary of what I have found so far. As to your specific problem, may the instance line causes it to break. I would just remove it or make it look like mine and see if that works. I am not sure how "add_relationship_filter" fits in all of this. At any point in the code, you can just write:
to show the SQL that would be produced. Maybe look at the SQL statement before and after the "add_relationship_filter" to see what the change is.
<connect from="dolly3d/assets" to="dolly3d/assets" instance_type="dolly3d/assets_in_assets" relationship="instance"/>
is problematic. The schema I have been using for testing is this:
Code:
<schema>
<search_type name="workflow/asset"/>
<search_type name="workflow/asset_in_asset"/>
<connect from="workflow/asset" to="workflow/asset" instance_type="workflow/asset_in_asset" relationship="instance"/>
<connect from="workflow/asset_in_asset" to="workflow/asset" relationship="code" from_col="parent_code" to_col="code" path="parent" type="many_to_many"/>
<connect from="workflow/asset_in_asset" to="workflow/asset" relationship="code" from_col="search_code" to_col="code" path="child" type="many_to_many"/>
</schema>Note that the instance connection has both to and from search types the same. This is problematic. The instance relationship was originally designed to work with one table to another different table with an "instance" table in between. When the "to" and the "from" tables are the same, do something like:
Code:
search = Search.eval("@SEARCH(workflow/asset)", asset)
print("search: ", search.get_statement() )
sobjects = search.get_sobjects()gives an SQL error complaining about referencing the "asset" table twice. This makes sense because the instance relationship never took this situation into account and some work needs to be done to get this to work. I tweaked the output SQL and it *should* look like this:
Code:
SELECT
"workflow"."public"."asset"."code"
FROM "workflow"."public"."asset"
LEFT OUTER JOIN "workflow"."public"."asset_in_asset"
ON "asset"."code" = "asset_in_asset"."search_code"
LEFT OUTER JOIN "workflow"."public"."asset" AS "asset2"
ON "asset_in_asset"."parent_code" = "asset2"."code"
WHERE ("asset"."s_status" != 'retired' or "asset"."s_status" is NULL)
and "asset2"."code" = 'ASSET00555'
ORDER BY "asset"."code";This would produce the correct result. Again this is going from the parent to children so we'd have to indicate a direction to go the other way around. It all gets very complicated using instances with tables to itself.
Expressions such as these do seem to work:
Code:
search = Search.eval("@SEARCH(parent:workflow/asset_in_asset.child:workflow/asset)", asset)This is explicit and clear which direction you are going, however, it's a bit tedious, but I think necessary to indicate how the flow goes until we have a better "instance" implementation for tables reference itself.
This is all a summary of what I have found so far. As to your specific problem, may the instance line causes it to break. I would just remove it or make it look like mine and see if that works. I am not sure how "add_relationship_filter" fits in all of this. At any point in the code, you can just write:
Code:
print("search: ", search.get_statement() )to show the SQL that would be produced. Maybe look at the SQL statement before and after the "add_relationship_filter" to see what the change is.