self to self relationship

[listy]

Hi, Diego.
For my own needs, I have achieved the right solution.
But i did not tested it with DropElementAction and DropElementWdg, as i don't use it for asset_to_assets relations, i just use scripting and api.

I ended up with this schema entries:
  <connect from="dolly3d/assets" to="dolly3d/assets" instance_type="dolly3d/assets_in_assets" relationship="instance"/>
  <connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="parent_assets_code" to_col="code" path="child" type="many_to_many"/>
  <connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="child_assets_code" to_col="code" path="parent" type="many_to_many"/>
  <connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="parent_assets_code" to_col="code" type="many_to_many"/>

And script to add and remove relationship:

Code:

def edit_multiple_instance_sobjects(project_code, insert_search_keys=[], exclude_search_keys=[], parent_key=None, instance_type=None, path=None):

    from pyasm.search import SearchType, Search

    server.set_project(project_code)

    def remove_instance(to_sobject, from_sobject, instance_type):
        from_expression = '@SOBJECT(child:{0})'.format(instance_type)
        to_expression = '@SOBJECT(parent:{0})'.format(instance_type)

        from_instances = Search.eval(from_expression, from_sobject)
        to_instances = Search.eval(to_expression, to_sobject)

        for from_instance in from_instances:
            from_search_key = from_instance.get_search_key()
            for to_instance in to_instances:
                to_search_key = to_instance.get_search_key()
                if from_search_key == to_search_key:
                    to_instance.delete()

    for search_key in insert_search_keys:

        child_search_type, child_code = server.split_search_key(search_key)
        parent_search_type, parent_code = server.split_search_key(parent_key)

        dst_sobject= server.query(parent_search_type, [('code', parent_code)], return_sobjects=True)[0]
        src_sobject = server.query(child_search_type, [('code', child_code)], return_sobjects=True)[0]

        instance = SearchType.create(instance_type)
        instance.add_related_connection(src_sobject, dst_sobject, src_path=path)
        instance.commit()

    for search_key in exclude_search_keys:

        child_search_type, child_code = server.split_search_key(search_key)
        parent_search_type, parent_code = server.split_search_key(parent_key)

        child_sobject = server.query(child_search_type, [('code', child_code)], return_sobjects=True)[0]
        parent_sobject = server.query(parent_search_type, [('code', parent_code)], return_sobjects=True)[0]

        if path:
            remove_instance(child_sobject, parent_sobject, instance_type)
        else:
            child_sobject.remove_instance(parent_sobject)

When i query my assets i also use "path", for example @SOBJECT(child:dolly3d/assets_in_assets['parent_assets_code', 'ASSETS00334'].parent:dolly3d/assets)

[listy]

Just checked the same schema in 4.8 and this doesn't work!

Getting SQL errors:
do_query error: SELECT count(*) FROM "aquariki"."public"."asset" LEFT OUTER JOIN "aquariki"."public"."asset_in_asset" ON "asset"."code" = "asset_in_asset"."parent_asset_code" LEFT OUTER JOIN "aquariki"."public"."asset" ON "asset_in_asset"."parent_asset_code" = "asset"."code" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) AND ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL)

Note here i am not making any query for asset_in_asset, my TEL looks like this @SOBJECT(tvs/asset_in_episode['episode_code', 'EPISODE00002'].tvs/asset)
Project named "aquarki"

---

Adding this to schema - breaks any relationship query in TACTIC, causing wrong results.
<connect from="tvs/asset" to="tvs/asset" instance_type="tvs/asset_in_asset" relationship="instance"/>

[listy]

To be more clear i do this:

Code:

expression_filter = "@SOBJECT(tvs/asset['asset_category_code', 'cat/subcat'])"

eval_sobjects = Search.eval(expression_filter)
if eval_sobjects:
    search.add_relationship_filters(eval_sobjects, op=op)
elif op == 'in':
    search.set_null_filter()

total_sobjects_query_count = search.get_count()

if i do simple eval for "@SOBJECT(tvs/asset['asset_category_code', 'cat/subcat'])" it is returning normal values.
Same with the "@SOBJECT(child:tvs/asset_in_asset['parent_asset_code', 'ASSET00002'].parent:tvs/asset)"

This is great, because it looks like the problem with add_relationship_filters method

[remkonoteboom]

I have created an issue:

https://github.com/Southpaw-TACTIC/TACTIC/issues/1446

and will look into this.

[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:

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.

[listy]

The schema is same for aquariki and dolly3d.
dolly3d - runs on 4.5
aquariki - runs on 4.8

The statement of search from code above:
'SELECT "aquariki"."public"."asset".*, "aquariki"."public"."asset"."code" as "_related_code" FROM "aquariki"."public"."asset" LEFT OUTER JOIN "aquariki"."public"."asset_in_asset" ON "asset"."code" = "asset_in_asset"."parent_asset_code" LEFT OUTER JOIN "aquariki"."public"."asset" ON "asset_in_asset"."parent_asset_code" = "asset"."code" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) ORDER BY "asset"."name"'

And this is with TACTIC 4.5 with the same schema:
'SELECT "dolly3d"."public"."assets".* FROM "dolly3d"."public"."assets" WHERE ("assets"."s_status" != \'retired\' or "assets"."s_status" is NULL) AND "assets"."code" in (\'ASSETS00377\', \'ASSETS00378\', \'ASSETS00379\', \'ASSETS00380\', \'ASSETS00381\', \'ASSETS00383\', \'ASSETS00384\', \'ASSETS00385\', \'ASSETS00387\', \'ASSETS00388\', \'ASSETS00389\', \'ASSETS00390\', \'ASSETS00391\', \'ASSETS00392\', \'ASSETS00394\', \'ASSETS00395\', \'ASSETS00396\', \'ASSETS00397\', \'ASSETS00398\', \'ASSETS00399\', \'ASSETS00400\', \'ASSETS00401\', \'ASSETS00402\', \'ASSETS00403\', \'ASSETS00404\', \'ASSETS00405\', \'ASSETS00406\', \'ASSETS00407\', \'ASSETS00408\', \'ASSETS00409\', \'ASSETS00410\', \'ASSETS00411\', \'ASSETS00412\', \'ASSETS00415\', \'ASSETS00416\', \'ASSETS00417\', \'ASSETS00418\', \'ASSETS00419\', \'ASSETS00420\', \'ASSETS00421\', \'ASSETS00422\', \'ASSETS00423\', \'ASSETS00425\', \'ASSETS00426\', \'ASSETS00427\', \'ASSETS00435\', \'ASSETS00436\', \'ASSETS00437\', \'ASSETS00438\', \'ASSETS00444\', \'ASSETS00468\', \'ASSETS00479\', \'ASSETS00480\', \'ASSETS00481\', \'ASSETS00497\', \'ASSETS00498\', \'ASSETS00499\', \'ASSETS00500\', \'ASSETS00501\', \'ASSETS00547\', \'ASSETS00548\', \'ASSETS00572\', \'ASSETS00573\', \'ASSETS00579\', \'ASSETS00587\', \'ASSETS00588\', \'ASSETS00601\', \'ASSETS00611\', \'ASSETS00638\', \'ASSETS00640\', \'ASSETS00642\', \'ASSETS00643\', \'ASSETS00655\') ORDER BY "assets"."name"'

As i see it. eval_sobjects = Search.eval("@SOBJECT(dolly3d/assets['assets_category_code', 'characters'])") getting sobjects, then
appends it to search.add_relationship_filters(eval_sobjects, op=op) and everything fine. But TACTIC 4.8 (may be 4.7 or even 4.6) do different things with added relationship.
I will do some camparsion between old and new add_relationship_filters method.

---

What i getting if i remove <connect from="workflow/asset" to="workflow/asset" instance_type="workflow/asset_in_asset" relationship="instance"/> and everything related to asset_in_asset:


'SELECT "aquariki"."public"."asset".* FROM "aquariki"."public"."asset" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) AND "asset"."code" in (\'ASSET00002\', \'ASSET00003\', \'ASSET00007\') ORDER BY "asset"."name"'



And this if i only remove these lines from schema and leave aseet-to asset instance line:
<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"/>


'SELECT "aquariki"."public"."asset".*, "aquariki"."public"."asset"."code" as "_related_code" FROM "aquariki"."public"."asset" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) ORDER BY "asset"."name"'

---

Codebase of add_relationship_filters in 4.5 and 4.5 is the same So may be it is scary!! sql.py

---

The schema for 4.5 Works:

<search_type name="dolly3d/assets" xpos="303" ypos="236"/>
<search_type name="dolly3d/assets_in_assets" xpos="696" ypos="136"/>
<connect from="dolly3d/assets" to="dolly3d/assets" instance_type="dolly3d/assets_in_assets" relationship="instance"/>
<connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="parent_assets_code" to_col="code" path="child" type="many_to_many"/>
<connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="child_assets_code" to_col="code" path="parent" type="many_to_many"/>
<connect from="dolly3d/assets_in_assets" to="dolly3d/assets" relationship="code" from_col="parent_assets_code" to_col="code" type="many_to_many"/>

The schema for 4.8 NOT

<search_type name="tvs/asset" type="manual" xpos="320" ypos="260"/>
<search_type name="tvs/asset_in_asset" type="manual" xpos="320" ypos="320"/>
<connect from="tvs/asset" to="tvs/asset" instance_type="tvs/asset_in_asset" relationship="instance"/>
<connect from="tvs/asset_in_asset" to="tvs/asset" relationship="code" from_col="parent_asset_code" to_col="code" path="child" type="many_to_many"/>
<connect from="tvs/asset_in_asset" to="tvs/asset" relationship="code" from_col="child_asset_code" to_col="code" path="parent" type="many_to_many"/>
<connect from="tvs/asset_in_asset" to="tvs/asset" relationship="code" from_col="parent_asset_code" to_col="code" type="many_to_many"/>


The only difference i see here is type="manual"

---

Statement for Search('tvs/asset') works as expected before add_relationship_filters:

'SELECT "aquariki"."public"."asset".* FROM "aquariki"."public"."asset" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) ORDER BY "asset"."name"'

[listy]

Ok, i tested add_relationship_filter instead add_relationship_filters
and it seems to work fine, but it uses ID's

'SELECT "aquariki"."public"."asset".* FROM "aquariki"."public"."asset" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) AND "asset"."id" = 7 ORDER BY "asset"."name"'

---

And if i do something like this:
search.add_relationship_filters(eval_sobjects, op=op, path='parent')

it also works no matter what path i pass here, child or parent:
'SELECT "aquariki"."public"."asset".* FROM "aquariki"."public"."asset" WHERE ("asset"."s_status" != \'retired\' or "asset"."s_status" is NULL) AND "asset"."code" in (\'ASSET00002\', \'ASSET00003\', \'ASSET00007\') ORDER BY "asset"."name"'

---

Code from add_relationship_filters of search.py:
attrs = schema.get_relationship_attrs(search_type, related_type, path=path, type=type)

When i not set path explicitly attrs ==:
{'from': 'tvs/asset', 'to': 'tvs/asset', 'instance_type': 'tvs/asset_in_asset', 'relationship': 'instance', 'from_col': 'code', 'to_col': 'code'}

When i not set path explicitly to 'child' attrs ==:
{'from': 'tvs/asset_in_asset', 'to': 'tvs/asset', 'relationship': 'code', 'from_col': 'child_asset_code', 'to_col': 'code', 'path': 'parent', 'type': 'many_to_many'}

[remkonoteboom]

I honestly don't know why "add_relationship_filter" does not use "add_relationship_filters". Somehow, it was implemented as a separate function with ever so slightly different logic and nobody really noticed until now.

It looks like 4.8 is trying to use joins, which is much faster and more scalable that the 4.5 method, but this breaks down when a table is joining to itself. It uses "asset" twice and the name becomes ambiguous. The SQL that I printed in my last post is what it should be. Not how I change the namespace of the second use of the asset table to "asset2" (or whatever name). This is a problem only when a table is trying to reference itself.

This is where "add_relationship_filters" breaks. There is a slight line difference:

In 4.5:
861 if search_type == related_type:

In 4.8:
911 if search_type == related_type and not attrs:

In 4.5, it ignored the relationship where as in 4.8 it tries to handle it. However, if the type is instance, this will break.

Try this:

911 if search_type == related_type and (not attrs or attrs.get("relationship") == "instance"):

This will use the 4.5 implementation for instance relationships for a table to itself. Until we can get the instance SQL to work as in my previous post, we will have to use the inefficient method.

Let me know if it works!

[listy]

911        if search_type == related_type and (not attrs or attrs.get("relationship") == "instance"):

This will use the 4.5 implementation for instance relationships for a table to itself.  Until we can get the instance SQL to work as in my previous post, we will have to use the inefficient method.

Let me know if it works!

This is works! Thanks!