Querying list of composite elements

mattcole81 · **Joined:** Tue Apr 22, 2008 7:17 am **Posts:** 14

Hi,

I have a question about querying collections of composite-elements.

I have mapping defined:

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                  assembly="OzoneHR.Domain"
                  namespace="OzoneHR.Domain.Questionnaires">
  <class name="QuestionTemplate" table="QuestionTemplate">
    <id name="Id" access="nosetter.camelcase-underscore"
        column="QuestionTemplateID" type="Int32">
      <generator class="identity" />
    </id>
    <property name="QuestionText" column="QuestionText" type="String" not-null="true" access="nosetter.camelcase-underscore" />    
    <bag name="SubDomains" 
         table="QuestionTemplateInSubdomain"
         cascade="all" 
         access="nosetter.camelcase-underscore"
         >
      <key column="QuestionTemplateID" />      
      <composite-element class="QuestionnaireSubdomain">        
        <property name="SubdomainName" access="nosetter.camelcase-underscore" column="Subdomain" not-null="true" />         
      </composite-element>      
    </bag>
    <bag name="Domains"
         table="QuestionTemplateInDomain"
         cascade="all"
         access="nosetter.camelcase-underscore"
         >
      <key column="QuestionTemplateID" />
      <composite-element class="QuestionnaireDomain">
        <property name="DomainName" access="nosetter.camelcase-underscore" column="Domain" not-null="true" />        
      </composite-element>
    </bag>
  </class>
</hibernate-mapping>

And what I'd like to do is write a query which accepted a list of Domains and another list of Subdomains and returned to me QuestionTemplates that had a match in their Domains list for any of the supplied domains, and a match in their Subdomains list for a match in any of the supplied subdomains.

Something equivalent to SQL like:

Code:

select distinct qt.questiontemplateid, questiontext 
from questiontemplate qt
join questiontemplateindomain d on d.questiontemplateid = qt.questiontemplateid
join questiontemplateinsubdomain s on s.questiontemplateid = qt.questiontemplateid
where qt.QuestionText LIKE '%question%'
and
   d.Domain in ('Domain', 'Another Domain')
and
   s.Subdomain in ('Subdomain', 'Subdomain2')

where the strings in the "in" clauses are the supplied lists.

Any ideas?

Thanks,
Matt

mattcole81 · **Joined:** Tue Apr 22, 2008 7:17 am **Posts:** 14

No one has any ideas?

Can anyone point me anywhere else on the web that might be able to help?

Thanks.

wolli · **Posted:** Fri May 09, 2008 3:18 am

What have you tried yet ? In HQL I would try something like this:

Code:

string[] domains = new string[] { 'Domain1', 'Domain 2' };
string[] subdomains = new string[] { 'Subdomain1', 'Subdomain 2' };

IQuery q  = session.CreateQuery("select distinct qt.Id, qt.QuestionText 
from QuestionTemplate qt
join QuestionTemplateinDomain d
join QuestionTemplateinSubdomain s
where qt.QuestionText like '%question%'
and
   d.Domain in (?)
and
   s.Subdomain in (?)");

q.SetParameter(0, domains);
q.SetParameter(1, subdomains);
...

mattcole81 · **Joined:** Tue Apr 22, 2008 7:17 am **Posts:** 14

Thanks very much for the reply.

I can't get it to work with the code you've put there, I think because the joins you are specifying aren't really valid as QuestionTemplateInDomain, and QuestionTemplateInSubdomain aren't entities in my hibernate mappings.

Rather, I have lists of composite-elements (QuestionnaireDomain and QuestionnaireSubdomain) that are populated from those database tables.

So, in my db I have a QuestionTemplate table that has (among other properties) an Id and a QuestionText field. I then have QuestionTemplateInDomain which is 1:M with QuestionTemplate, each row having the QuestionTemplateId and the Domain. (An identical scenario exists for QuestionTemplateInSubdomain.)

In my domain, I have a QuestionnaireDomain Value Object, which simply has a DomainName property.

Population of this setup works fine with the mapping above, but this query is causing me some dramas. I seem to have some issues with querying on components/composite-elements (I have another couple of posts on here about similar kind of stuff), so it might be that I'm forced to redo my datamodel to get around the problems. But, it seems from the documentation and books I have that I should be able to do what I'm trying to do somehow!

Thanks,
Matt

wolli · **Posted:** Fri May 09, 2008 7:08 am

Have you tried the query without the joins ?

Code:

from QuestionTemplate qt
where qt.QuestionText like '%question%'
and
   qt.Domains.Name in (?)
and
   qt.Subdomains.Name in (?)

Is there a special reason for mapping them as composite elements ?

mattcole81 · **Joined:** Tue Apr 22, 2008 7:17 am **Posts:** 14

Hi Wolfgang,

Only that they are value objects in my domain not entities. They are quite literally just a string value. However the concept is used in more than one place in the domain. If I can't solve my querying issues I may be forced to create a separate table and map it as an entity in NHibernate.

The code you've suggested without the join doesn't work for me, but I have actually managed to resolve this particular issue. Kind of. My code now looks like this:

Code:

public IList<QuestionTemplate> GetQuestionTemplateByCriteria(string questionText, List<QuestionnaireDomain> domains,
            List<QuestionnaireSubdomain> subdomains)
{
    var domainNames = from domain in domains
                      select domain.DomainName;

    var subdomainNames = from subdomain in subdomains
                        select subdomain.SubdomainName;

    ISession session = _sessionManager.OpenSession();
    StringBuilder queryText = new StringBuilder();
    queryText.Append("select distinct qt from QuestionTemplate qt join qt.Domains d join qt.SubDomains s where qt.QuestionText like :questionText");
    if (domains.Count > 0) queryText.Append(" AND d.DomainName in (:domainNames)");
    if (subdomains.Count > 0) queryText.Append(" AND s.SubdomainName in (:subdomainNames)");

    IQuery query = session.CreateQuery(queryText.ToString())
        .SetString("questionText", String.Format("%{0}%", questionText))
                                .SetParameterList("domainNames", domainNames)
                                .SetParameterList("subdomainNames", subdomainNames);

    return query.List<QuestionTemplate>();
}

So I can't query on the composite-element object itself, which I guess makes sense so instead I just query by the name property of each of them.

Thanks again for your help!