ISQLQuery subquery TO ICriteria??

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

Hibernate version: 1.2.1.GA

I have the following SQL Query:

Code:

SELECT   Company.*
FROM   Company
WHERE   Company.DeletedOn IS NULL
AND      Company.CompanyID IN
   (
      SELECT   Site.CompanyID
      FROM   Site
               INNER JOIN SiteProduct
               ON   Site.SiteID = SiteProduct.SiteID
               AND   SiteProduct.DeletedOn IS NULL
               AND   SiteProduct.IsProduced = 1
                  INNER JOIN Product
                  ON   SiteProduct.ProductID = Product.ProductID
                  AND   Product.DeletedOn IS NULL
                  AND   Product.IsPublic = 1
      WHERE      Site.DeletedOn IS NULL
      GROUP BY   Site.CompanyID
   )

Which works fine using this

Code:

ISQLQuery sqlQuery = Session.CreateSQLQuery(stringQuery.ToString()).AddEntity(typeof(Company));
IList<Company> companies = sqlQuery.List<Company>();

However I have been unsuccessful at translating this to ICriteria. Can you help? Below are my attempts:

Code:

DetachedCriteria dcCompany = DetachedCriteria.For(typeof(Company));
dcCompany.Add(Expression.IsNull("DeletedOn"));
dcCompany.SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("Sites"))
    );

DetachedCriteria dcSite = dcCompany.CreateCriteria("Sites");
dcSite.Add(Expression.IsNull("DeletedOn"));

DetachedCriteria dcSiteProduct = dcSite.CreateCriteria("SiteProducts");
dcSiteProduct.Add(Expression.IsNull("DeletedOn"));
dcSiteProduct.Add(Expression.Eq("IsProduced", true));

DetachedCriteria dcProduct = dcSiteProduct.CreateCriteria("Product");
dcProduct.Add(Expression.Eq("IsPublic", true));

ICriteria cCompany = Session.CreateCriteria(typeof(Company));
cCompany.SetMaxResults(maxRecords);
cCompany.Add(Subqueries.In("CompanyID", dcCompany));

and

Code:

ICriteria cCompany = Session.CreateCriteria(typeof(Company));
cCompany.SetMaxResults(maxRecords);
cCompany.Add(Expression.IsNull("DeletedOn"));
cCompany.SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("Sites"))
    );

ICriteria cSite = cCompany.CreateCriteria("Sites");
cSite.Add(Expression.IsNull("DeletedOn"));

ICriteria cSiteProduct = cSite.CreateCriteria("SiteProducts");
cSiteProduct.Add(Expression.IsNull("DeletedOn"));
cSiteProduct.Add(Expression.Eq("IsProduced", true));

ICriteria cProduct = cSiteProduct.CreateCriteria("Product");
cProduct.Add(Expression.Eq("IsPublic", true));

companies = cCompany.List<Company>();

wolli · **Posted:** Thu May 22, 2008 2:11 am

Do you have associations from Site to SiteProduct and from SiteProduct to Product ? Then the criteria would be something like this:

Code:

DetachedCriteria dcSite = dcSite.CreateCriteria("Sites", "s")
    .Add(Expression.IsNull("DeletedOn"))
    .SetProjection(Projections.ProjectionsList()
        .Add(Projection.GroupProperty("s.CompanyID"))
        .Add(Projection.Property("s.CompanyID"))
    )
    .CreateCriteria("SiteProducts")
        .Add(Expression.IsNull("DeletedOn"))
        .Add(Expression.Eq("IsProduced", true))
        .CreateCriteria("Products")
            .Add(Expression.Eq("IsPublic", true));

ICriteria cCompany = Session.CreateCriteria(typeof(Company));
   .SetMaxResults(maxRecords)
   .Add(Expression.IsNull("DeletedOn")
   .Add(Subqueries.In("CompanyID", dcSite )); 

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

Thanks for your reply!

Yes I have associations from Site to SiteProduct and from SiteProduct to Product.

I'll give this a try but your first line of code does not look right. Should it be:

Code:

DetachedCriteria dcSite = DetachedCriteria.For(typeof(Site), "s")

wolli · **Posted:** Thu May 22, 2008 8:48 am

Yes, should be a detached criteria. Sorry, copy and paste ...

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

I tried it and other variations and still no luck. I'll keep plugging away to see if I can get it resolved.

wolli · **Posted:** Thu May 22, 2008 10:34 am

What did not work ? Did you get the wrong result or an exception ? Can you post exception and/or the generated sql statement ?

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

I get a MappingExcpetion: "Unknown entity class: System.String"
I'm not sure if Subqueries.In() can referance a field in this manner.

Here is the actual code I'm using:

Code:

                DetachedCriteria dcSite = DetachedCriteria.For(typeof(Site), "s");
                dcSite.Add(Expression.IsNull("DeletedOn"));
                dcSite.SetProjection(Projections.ProjectionList()
                        .Add(Projections.GroupProperty("s.Company"))
                    )
                    .CreateCriteria("SiteProducts")
                        .Add(Expression.IsNull("DeletedOn"))
                        .Add(Expression.Eq("IsProduced", true))
                        .CreateCriteria("Product")
                            .Add(Expression.Eq("IsPublic", true));

                ICriteria cCompany = Session.CreateCriteria(typeof(Company), "c");
                cCompany.SetMaxResults(maxRecords);
                cCompany.Add(Subqueries.In("c.ID", dcSite));

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

I GOT IT WORKING!! :)

the method I should be using for Subqueries is PropertyIn().

cCompany.Add(Subqueries.PropertyIn("c.ID", dcSite));

Thanks for pointing me in the right direction. I'll give you credit.

wolli · **Posted:** Thu May 22, 2008 12:05 pm

Sorry, could have told you that. Apparently I didn't look carefully enough at your code and just copied & pasted it. But at least it's working now.

kids_pro · **Joined:** Sun Oct 16, 2005 4:54 am **Posts:** 7

wolli wrote:

Code:

DetachedCriteria dcSite = dcSite.CreateCriteria("Sites", "s")
    .Add(Expression.IsNull("DeletedOn"))
    .SetProjection(Projections.ProjectionsList()
        .Add(Projection.GroupProperty("s.CompanyID"))
        .Add(Projection.Property("s.CompanyID"))
    )

Is it a typos when you projection "s.CompanyID" twice?

wolli · **Posted:** Mon May 26, 2008 3:01 am

Not intenionally, one for the grouping and one for restricitng the select to s.CompanyId. Maybe that's not explicitly necessary, I haven't tried.