Issues using lists

[martirm]

Hello,
I am struggling a bit with the use of lists and applying functions to them. This is a simplified example of what I'm trying to do:

HasAttribute(object_id: "1", attribute_id: "01", attribute_name: "A");
HasAttribute(object_id: "1", attribute_id: "02", attribute_name: "B");
HasAttribute(object_id: "2", attribute_id: "01", attribute_name: "A");

Object(object_id: "1", object_name: "first_object");
Object(object_id: "2", object_name: "second_object");
Object(object_id: "3", object_name: "third_object");

HasAttribute_f(name, object_id:) = attribute_id :-
    HasAttribute(object_id:, attribute_id:, attribute_name: name);

HasAttributesAnd(name_list, object_id:, attribute_id_list:) :-
    attribute_id_list List= (HasAttribute_f(name, object_id:) :- name in name_list);

Query(object_id:, object_name:, attribute_id_list:) :-
    HasAttributesAnd(name_list, object_id:, attribute_id_list:),
    Object(object_id:, object_name:),
    Size(attribute_id_list) == Size(name_list),
    name_list == ["A", "B"];

Basically I have a table of objects and a table of attributes associated with those objects. And given a list of any number of attributes, I want to return the objects that include all of them. Currently this works, but only because I forced the sizes of attribute_id_list and name_list to match, (otherwise I would just get all objects). This solution, however, is very slow on large tables, and it seems like there should be a more elegant and efficient way to do it.

Thank you.

[EvgSkv]

Hello @martirm !
This is an interesting question.
As I started implementing it I hit duckdb bug and filed an issue.
So I'm adjusting the query to avoid the bug.

Generally unions are easier for machines compute than overlaps, so if performance is a concern, then trying to express logic via unions may be a way to go.

Here is my shot at this problem with the goal to keep complexity under control:
https://colab.research.google.com/drive/1DaHmaC1yu6LaRuYrUtwH7fQzHOvmcV9X?usp=sharing

Would you like to try applying it to your data and let me know if it's performant enough?

Thank you!
Evgeny.

[martirm]

Thank you, @EvgSkv ! I tried your solution on my data and there is a huge improvement, the queries are now running quite fast. I still need to test it more thoroughly on other tables that follow a similar structure, but it looks like this is the way to go.