[GitHub] [cloudstack] weizhouapache commented on a change in pull request #5521: server: cannot deploy/start vm if service offering has multiple tags

[GitBox <gi...@apache.org>]

weizhouapache commented on a change in pull request #5521:
URL: https://github.com/apache/cloudstack/pull/5521#discussion_r718188225



##########
File path: engine/schema/src/main/java/com/cloud/host/dao/HostDaoImpl.java
##########
@@ -85,7 +85,7 @@
                                                              + "GROUP BY host_id "
                                                              + "HAVING tag_count = %s ";
     private static final String SEPARATOR = ",";
-    private static final String LIST_CLUSTERID_FOR_HOST_TAG = "select distinct cluster_id from host join host_tags on host.id = host_tags.host_id and host_tags.tag = ?";
+    private static final String LIST_CLUSTERID_FOR_HOST_TAG = "select distinct cluster_id from host join ( %s ) AS selected_hosts ON host.id = selected_hosts.host_id";

Review comment:
       > Should `%s` be replaced with `? `
   
   @rhtyd 
   see suresh's comment above.
   
   https://github.com/apache/cloudstack/blob/fccd02b6ced1ebe88a7bfd606297f339e119d5c5/engine/schema/src/main/java/com/cloud/host/dao/HostDaoImpl.java#L1208-L1213
   
   an example of final sql is (if there are 3 tags)
   ```
   select distinct cluster_id from host join 
   ( SELECT filtered.host_id, COUNT(filtered.tag) AS tag_count FROM (SELECT host_id, tag FROM host_tags GROUP BY host_id,tag) AS filtered WHERE tag IN(?,?,?) GROUP BY host_id HAVING tag_count = 3  ) AS selected_hosts 
   ON host.id = selected_hosts.host_id;
   ```




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org