I have decided to go for the second alternative with an bidirectional many-to-many association.
This is pretty much what andromeda generated for me:
Code:
<joined-subclass name="MediaImpl" table="MEDIA" dynamic-insert="false" dynamic-update="false" abstract="false">
<key foreign-key="MEDIAIFKC">
<column name="ID" sql-type="BIGINT"/>
</key>
<set name="tag" table="MEDIA2TAG" order-by="TAG_FK" lazy="true" fetch="select" inverse="false">
<key foreign-key="TAG_MEDIA_FKC">
<column name="MEDIA_FK" sql-type="BIGINT"/>
</key>
<many-to-many class="TagImpl" foreign-key="MEDIA_TAG_FKC">
<column name="TAG_FK" sql-type="BIGINT"/>
</many-to-many>
</set>
</joined-subclass>
<joined-subclass name="TagImpl" table="TAG" dynamic-insert="false" dynamic-update="false" abstract="false">
<key foreign-key="TAGIFKC">
<column name="ID" sql-type="BIGINT"/>
</key>
<property name="tagName" type="java.lang.String">
<column name="TAG_NAME" not-null="true" unique="false" sql-type="VARCHAR(255) BINARY"/>
</property>
<set name="media" table="MEDIA2TAG" order-by="MEDIA_FK" lazy="true" fetch="select" inverse="true">
<key foreign-key="MEDIA_TAG_FKC">
<column name="TAG_FK" sql-type="BIGINT"/>
</key>
<many-to-many class="MediaImpl" foreign-key="TAG_MEDIA_FKC">
<column name="MEDIA_FK" sql-type="BIGINT"/>
</many-to-many>
</set>
</joined-subclass>
TAG and MEDIA are subclasses to a superclass COMPONENT but I don't think it is relevant for my question.
In order to get all tags I run:
Code:
Query q = session.createQuery("select t from Tag t");
This gives me a collection of all distinct tags Thats works fine. Now I have to create a data transfer object to present in my view. The dto shall consist of the tagname and the number of medias containing just that tag.
By design I call a method toTagListItem() with all tags as collection from the query result as a parameter. In this method toTagListItem() I want to count all medias containing the tag. This can only be done here or I break the code architecture we agreed upon in my team.
I try this:
Code:
select count(t.media) from Tag t left join fetch t.media
where Tag is an object from the collection (the method iterates through all tags).
But I get:
Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax t
o use near ') as col_0_0_, mediaimpl2_.ID as ID0_1_, mediaimpl2_.ID as ID0_, mediaimpl2_1_.D' at line 1
I understand my query is not correct. Could someone help me with this?
Thanks.
Fredrik
Login
FAQ
Search