)]}'
{"/PATCHSET_LEVEL":[{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"1f77dcd437a50e0ad2abcbbb9121a1893b917d4d","unresolved":false,"context_lines":[],"source_content_type":"","patch_set":6,"id":"1f6cc7ba_2f84e630","updated":"2022-02-08 03:02:41.000000000","message":"Inline things can be in a followup.","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"a1bcce2784e0976f3b99c4a594e3307a0e9b9add","unresolved":false,"context_lines":[],"source_content_type":"","patch_set":6,"id":"307cd78b_bcc9b745","updated":"2022-02-07 22:36:39.000000000","message":"Looks reasonable to me, just a couple of typos I think.","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"44b646c54bf370c8d48c8dbe7f9683c5faec4b50","unresolved":false,"context_lines":[],"source_content_type":"","patch_set":7,"id":"6fc5d996_818b25a7","updated":"2022-02-08 21:48:42.000000000","message":"Looks good","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":11604,"name":"sean mooney","email":"smooney@redhat.com","username":"sean-k-mooney"},"change_message_id":"688b37452ef93c162c425e74e7f5f5773eee680a","unresolved":false,"context_lines":[],"source_content_type":"","patch_set":7,"id":"0c52d323_b7e63147","updated":"2022-02-16 15:10:37.000000000","message":"i think the join approach is likely sub-optimal but its been a while since i worked with SQL directly.\n\ni have proposed an alternative algorithm based on representing each group as set of sub queries.","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":11604,"name":"sean mooney","email":"smooney@redhat.com","username":"sean-k-mooney"},"change_message_id":"e61a82707aadbd279d04122ae6a3ae21b62dfa7a","unresolved":false,"context_lines":[],"source_content_type":"","patch_set":7,"id":"28204337_f0c6556d","updated":"2022-02-22 14:49:12.000000000","message":"lets proceed with this as it is for now and we can consider using subquiries in a followup if we find the inner joins are infact a significant performace hit.\n\ni have review the entire series now and am more or less happy with the majority of this so i think we can start merging the early patches so +w","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"}],"placement/objects/research_context.py":[{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"a1bcce2784e0976f3b99c4a594e3307a0e9b9add","unresolved":true,"context_lines":[{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_aggregates AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_aggregates AS rpt3"}],"source_content_type":"text/x-python","patch_set":6,"id":"bddc7dec_6a8cf61e","line":1050,"range":{"start_line":1050,"start_character":29,"end_line":1050,"end_character":39},"updated":"2022-02-07 22:36:39.000000000","message":"traits?","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":9708,"name":"Balazs Gibizer","display_name":"gibi","email":"gibizer@gmail.com","username":"gibi"},"change_message_id":"107f68e0d89346789fa9d30791e3df8d9d3145f6","unresolved":false,"context_lines":[{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_aggregates AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_aggregates AS rpt3"}],"source_content_type":"text/x-python","patch_set":6,"id":"00c1fae8_690e771c","line":1050,"range":{"start_line":1050,"start_character":29,"end_line":1050,"end_character":39},"in_reply_to":"bddc7dec_6a8cf61e","updated":"2022-02-08 14:51:00.000000000","message":"Done","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"a1bcce2784e0976f3b99c4a594e3307a0e9b9add","unresolved":true,"context_lines":[{"line_number":1050,"context_line":"    # JOIN resource_provider_aggregates AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_aggregates AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."}],"source_content_type":"text/x-python","patch_set":6,"id":"4c5a1cbd_6a7ad97d","line":1053,"range":{"start_line":1053,"start_character":29,"end_line":1053,"end_character":39},"updated":"2022-02-07 22:36:39.000000000","message":"traits?","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":9708,"name":"Balazs Gibizer","display_name":"gibi","email":"gibizer@gmail.com","username":"gibi"},"change_message_id":"107f68e0d89346789fa9d30791e3df8d9d3145f6","unresolved":false,"context_lines":[{"line_number":1050,"context_line":"    # JOIN resource_provider_aggregates AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_aggregates AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."}],"source_content_type":"text/x-python","patch_set":6,"id":"9d66bc60_dac71a83","line":1053,"range":{"start_line":1053,"start_character":29,"end_line":1053,"end_character":39},"in_reply_to":"4c5a1cbd_6a7ad97d","updated":"2022-02-08 14:51:00.000000000","message":"Done","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":4690,"name":"melanie witt","display_name":"melwitt","email":"melwittt@gmail.com","username":"melwitt"},"change_message_id":"a1bcce2784e0976f3b99c4a594e3307a0e9b9add","unresolved":true,"context_lines":[{"line_number":1071,"context_line":"    if rp_ids:"},{"line_number":1072,"context_line":"        sel \u003d sel.where(rp_tbl.c.id.in_(rp_ids))"},{"line_number":1073,"context_line":"    return set(r[0] for r in context.session.execute(sel))"},{"line_number":1074,"context_line":""},{"line_number":1075,"context_line":""},{"line_number":1076,"context_line":"@db_api.placement_context_manager.reader"},{"line_number":1077,"context_line":"def get_provider_ids_having_any_trait(ctx, traits):"}],"source_content_type":"text/x-python","patch_set":6,"id":"0d0acdc7_224745c2","line":1074,"updated":"2022-02-07 22:36:39.000000000","message":"Whew, took me awhile to remotely understand this ^ (not your fault) and it seems to make sense.","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":9708,"name":"Balazs Gibizer","display_name":"gibi","email":"gibizer@gmail.com","username":"gibi"},"change_message_id":"107f68e0d89346789fa9d30791e3df8d9d3145f6","unresolved":false,"context_lines":[{"line_number":1071,"context_line":"    if rp_ids:"},{"line_number":1072,"context_line":"        sel \u003d sel.where(rp_tbl.c.id.in_(rp_ids))"},{"line_number":1073,"context_line":"    return set(r[0] for r in context.session.execute(sel))"},{"line_number":1074,"context_line":""},{"line_number":1075,"context_line":""},{"line_number":1076,"context_line":"@db_api.placement_context_manager.reader"},{"line_number":1077,"context_line":"def get_provider_ids_having_any_trait(ctx, traits):"}],"source_content_type":"text/x-python","patch_set":6,"id":"82d69c6e_09034f35","line":1074,"in_reply_to":"0d0acdc7_224745c2","updated":"2022-02-08 14:51:00.000000000","message":"As you saw above I copied this from the aggregate query as there we already support the in: relationship. The logic is to take each in: query and individually filter down the list of RPs based on it and the join these filters.","commit_id":"153aa992b13378a9d0561cbd837906e2c6c1ec1f"},{"author":{"_account_id":11604,"name":"sean mooney","email":"smooney@redhat.com","username":"sean-k-mooney"},"change_message_id":"688b37452ef93c162c425e74e7f5f5773eee680a","unresolved":true,"context_lines":[{"line_number":1023,"context_line":"    # parameter of the new nested structure with the same meaning."},{"line_number":1024,"context_line":"    # This code should be removed once each caller is adapted to call this"},{"line_number":1025,"context_line":"    # with the new structure"},{"line_number":1026,"context_line":"    if all(not isinstance(trait, set) for trait in required_traits):"},{"line_number":1027,"context_line":"        # old value: required_traits \u003d [A, B, C] -\u003e A and B and C"},{"line_number":1028,"context_line":"        # new value: required_traits \u003d [{A}, {B}, {C}] -\u003e (A) and (B) and (C)"},{"line_number":1029,"context_line":"        # the () part could be a set of traits with OR relationship but"}],"source_content_type":"text/x-python","patch_set":7,"id":"0848130f_697f0802","line":1026,"updated":"2022-02-16 15:10:37.000000000","message":"ack\nmakes sense as it allow this change to remain local db query without modifyign the call signiture","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":11604,"name":"sean mooney","email":"smooney@redhat.com","username":"sean-k-mooney"},"change_message_id":"688b37452ef93c162c425e74e7f5f5773eee680a","unresolved":true,"context_lines":[{"line_number":1044,"context_line":"    # SELECT"},{"line_number":1045,"context_line":"    #   rp.id"},{"line_number":1046,"context_line":"    # FROM resource_providers AS rp"},{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_traits AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_traits AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."},{"line_number":1057,"context_line":"    # WHERE rp.id IN ($RP_IDs)"},{"line_number":1058,"context_line":""}],"source_content_type":"text/x-python","patch_set":7,"id":"5002cc5d_f3603f03","line":1055,"range":{"start_line":1047,"start_character":3,"end_line":1055,"end_character":53},"updated":"2022-02-16 15:10:37.000000000","message":"i propably whould have built this out of subquires rahter then multipel joins like this since we dont want to actully project the joined tabel we just want to generate the set of rp ides based on the traits  and then filter then return the result set.\n\nselect rp.id from resource_providers as rp\nwhere rp.id in (\n  select id from \n    (select id as id from resource_providers as rps\n    join resource_provider_tratis as rpt\n    on  rp.id \u003d rpt1.resource_provider_id\n    and rpt.trait_id in ($TRAIT1_ID)) as rp1,\n    (select id as id from resource_providers as rps\n    join resource_provider_tratis as rpt\n    on  rps.id \u003d rpt.resource_provider_id\n    and rpt.trait_id in ($TRAIT2_ID)) as rp2,\n    (select id as id from resource_providers as rps\n    join resource_provider_tratis as rpt\n    on  rps.id \u003d rpt.resource_provider_id\n    and rpt.trait_id in ($TRAIT3_ID,$TRAIT3_ID)) as rp3\n    where id in rp1 and id in rp2 and id in rp3\n  )\n\ni think that would use less memory and be faster in general\n\ni have not tested that obvioulsy but since we dont need anythin but the rp id\ni think this woudl work better espcially as the number of groups increases.","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":11604,"name":"sean mooney","email":"smooney@redhat.com","username":"sean-k-mooney"},"change_message_id":"e61a82707aadbd279d04122ae6a3ae21b62dfa7a","unresolved":true,"context_lines":[{"line_number":1044,"context_line":"    # SELECT"},{"line_number":1045,"context_line":"    #   rp.id"},{"line_number":1046,"context_line":"    # FROM resource_providers AS rp"},{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_traits AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_traits AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."},{"line_number":1057,"context_line":"    # WHERE rp.id IN ($RP_IDs)"},{"line_number":1058,"context_line":""}],"source_content_type":"text/x-python","patch_set":7,"id":"0a6617e2_94c4695b","line":1055,"range":{"start_line":1047,"start_character":3,"end_line":1055,"end_character":53},"in_reply_to":"045a5404_86930031","updated":"2022-02-22 14:49:12.000000000","message":"in generally i think the subquires can allow more paralelsium but most dbms system will optimise the execution plance such that both will be simialr yes.","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":7166,"name":"Sylvain Bauza","email":"sbauza@redhat.com","username":"sbauza"},"change_message_id":"6299f8ec1edf0a885202e164f22611d7d99be6cb","unresolved":true,"context_lines":[{"line_number":1044,"context_line":"    # SELECT"},{"line_number":1045,"context_line":"    #   rp.id"},{"line_number":1046,"context_line":"    # FROM resource_providers AS rp"},{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_traits AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_traits AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."},{"line_number":1057,"context_line":"    # WHERE rp.id IN ($RP_IDs)"},{"line_number":1058,"context_line":""}],"source_content_type":"text/x-python","patch_set":7,"id":"045a5404_86930031","line":1055,"range":{"start_line":1047,"start_character":3,"end_line":1055,"end_character":53},"in_reply_to":"259ed175_94ba32fe","updated":"2022-02-22 10:14:40.000000000","message":"I\u0027m not a SQL guru, but AFAIC the executions plans between JOINS or subqueries are quite identical from a DBMS perspective.\nI\u0027d rather stick with the existing pattern of INNER JOINing the different tables here as it\u0027s closer to what the SQL query does actually even if the subquery is better understood.\n\nMaybe we could change it in a FUP but I\u0027d prefer first to see the execution plans for both by using EXPLAIN.","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"},{"author":{"_account_id":9708,"name":"Balazs Gibizer","display_name":"gibi","email":"gibizer@gmail.com","username":"gibi"},"change_message_id":"b65ec823fe73b00556416f96b87fc4a21537871e","unresolved":true,"context_lines":[{"line_number":1044,"context_line":"    # SELECT"},{"line_number":1045,"context_line":"    #   rp.id"},{"line_number":1046,"context_line":"    # FROM resource_providers AS rp"},{"line_number":1047,"context_line":"    # JOIN resource_provider_traits AS rpt1"},{"line_number":1048,"context_line":"    #   ON rp.id \u003d rpt1.resource_provider_id"},{"line_number":1049,"context_line":"    #   AND rpt1.trait_id IN ($TRAIT1_ID)"},{"line_number":1050,"context_line":"    # JOIN resource_provider_traits AS rpt2"},{"line_number":1051,"context_line":"    #   ON rp.id \u003d rpt2.resource_provider_id"},{"line_number":1052,"context_line":"    #   AND rpt2.trait_id IN ($TRAIT2_ID)"},{"line_number":1053,"context_line":"    # JOIN resource_provider_traits AS rpt3"},{"line_number":1054,"context_line":"    #   ON rp.id \u003d rpt3.resource_provider_id"},{"line_number":1055,"context_line":"    #   AND rpt3.trait_id IN ($TRAIT3_ID, $TRAIT4_ID)"},{"line_number":1056,"context_line":"    # # Only if we have rp_ids..."},{"line_number":1057,"context_line":"    # WHERE rp.id IN ($RP_IDs)"},{"line_number":1058,"context_line":""}],"source_content_type":"text/x-python","patch_set":7,"id":"259ed175_94ba32fe","line":1055,"range":{"start_line":1047,"start_character":3,"end_line":1055,"end_character":53},"in_reply_to":"5002cc5d_f3603f03","updated":"2022-02-17 09:52:48.000000000","message":"I steal the idea from the aggregate query https://github.com/openstack/placement/blob/master/placement/objects/research_context.py#L907-L953 that uses the same JOIN structure. I can try to you proposal today / tomorrow.","commit_id":"a5ebaf2a45de70cc0725f248943bb9a1a9cb9a49"}]}