Hi,
I am new to NHibernate and I am trying to learn how to query my data.
Below is the configuration xml. Only the recipe is shown.
I want to be able to query recipes by recipetitle from keywords entered and also ingredients from ingredientname.
So you might enter "pasta wine" for example.
This is what I have tried but gives me an error.
hql = "from Recipe r " +
"left join r.Images " +
"inner join r.User " +
"inner join r.Ingredients i " +
"where i.IngredientName Like '%pasta%' OR i.IngredientName Like '%wine%' OR r.RecipeTitle Like '%pasta' OR r.RecipeTitle Like '%wine%'";
I want to eager load the collections as well.
Am I going about querying right?? I need to able to build the query string from my search criteria. This would be easy form me in SQL.
Malcolm
<class name="Recipe" table="Recipes" xmlns="urn:nhibernate-mapping-2.2">
<id name="RecipeID" type="Int32" column="RecipeID">
<generator class="identity" />
</id>
<property name="RecipeTitle" type="String">
<column name="RecipeTitle" />
</property>
<property name="Completed" type="Boolean">
<column name="Completed" />
</property>
<property name="ModifiedOn" type="DateTime">
<column name="ModifiedOn" />
</property>
<property name="Rating" type="Double">
<column name="Rating" />
</property>
<property name="PrepTime" type="Int32">
<column name="PrepTime" />
</property>
<property name="CookTime" type="Int32">
<column name="CookTime" />
</property>
<property name="Method" type="String">
<column name="Method" />
</property>
<bag name="Images" inverse="true" cascade="all">
<key column="RecipeID" />
<one-to-many class="OurRecipes.Domain.RecipeImage, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</bag>
<many-to-one name="Category" column="CategoryID" />
<bag name="Comments" inverse="true" cascade="all">
<key column="RecipeID" />
<one-to-many class="OurRecipes.Domain.Comment, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</bag>
<many-to-one name="User" column="EnteredByID" />
<bag name="Ingredients" inverse="true" cascade="all">
<key column="RecipeID" />
<one-to-many class="OurRecipes.Domain.Ingredient, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</bag>
</class>
From stackoverflow
-
To build dynamic queries, I would use the criteria API. This makes the dynamic query much more stable, because you don't need string operations to build it up.
ICriteria query = Session.CreateCriteria(typeof(Recipe), "r") .CreateCriteria("Ingredients", "i", JoinType.InnerJoin) .Add( Expression.Disjunction() // OR .Add(Expression.Like("i.IngredientName", "%pasta%")) .Add(Expression.Like("i.IngredientName", "%wine%")) .Add(Expression.Like("r.RecipeTitle", "%pasta%")) .Add(Expression.Like("r.RecipeTitle", "%wine%"))); List<Recipe> result = query.List<Recipe>();Edit:
For eager loading you could set the fetch-mode:
ICriteria query = Session.CreateCriteria(typeof(Recipe), "r") .SetFetchMode("Images", FetchMode.Join) .SetFetchMode("Comments", FetchMode.Join) .SetFetchMode("Ingredients", FetchMode.Join)But I wouldn't do this because you get the results multiplied by the number of Images, Comments and Ingredients. So if you had 4 Images, 2 Comments and 12 Ingredients, you get your recipe 96 times. You don't recognize this, because NHibernate puts the things together again, but it generates traffic between the application and the database. So better let NHibernate load it with separate queries.
One more edit to show dynamic query composition.
// filter arguments, all are optional and should be omitted if null List<string> keywords; TimeSpan? minCookingTime; TimeSpan? maxCookingTime; int? minRating; int? maxRating; ICriteria query = Session.CreateCriteria(typeof(Recipe), "r"); if (keyword != null) { // optional join query.CreateCriteria("Ingredients", "i", JoinType.InnerJoin); // add keyword search on ingredientName and RecipeTitle var disjunction = Expression.Disjunction(); foreach (string keyword in keywords) { string pattern = String.Format("%{0}%", keyword); disjunction .Add(Expression.Like("i.IngredientName", pattern)) .Add(Expression.Like("r.RecipeTitle", pattern)); } query.Add(disjunction) } if (minCookingTime != null) { query.Add(Expression.Ge(r.CookingTime, minCookingTime.Value)); } if (maxCookingTime != null) { query.Add(Expression.Le(r.CookingTime, maxCookingTime.Value)); } if (minRating != null) { query.Add(Expression.Ge(r.Rating, minRating.Value)); } if (maxRating != null) { query.Add(Expression.Le(r.Rating, maxRating.Value)); }Frederik Gheysels : In order to solve this problem, you can get the DistinctRootEntityResultTransformerChad Ruppert : Why would you use FetchMode.Join instead of FetchMode.Eager for these loads if you were truly intending to use the child objects?Malcolm : I don't see how that is dynamic when you have hard coded the keywords. How would you query if i gave you a string of words seperated by spaces???Stefan Steinegger : @Malcolm: I don't really understand the question. This implementation is not dynamic, it's an example to show how it works. It is very easy to add expressions dynamically - instead of building up an HQL string which could have syntax errors (eg. when no filter is given and you write an empty WHERE clause). In our project, I even add subqueries and joins dynamically, only if they are needed. Using criteria, this is simple, with HQL you'd have troubles.Stefan Steinegger : @Chad: FetchMode.Join and FetchMode.Eager are the same. https://www.hibernate.org/hib_docs/v3/api/org/hibernate/FetchMode.htmlMalcolm : Ok fine. Can you give me an exmaple of how you would do as I said. Given a string of words create a Criteria query, assume that the field being searched is RecipeTitle. I do not understand how you would do this????Malcolm : Let me explain what i am doing. I have a search form where the user can enter keywords which searches RecipeTitle or an IngredientName or enter a range for cooking time or a range for the recipe rating etc. So I want to build a criteria query from this search information. So this would be dynamic right. Could you explain one way of doing this please?Stefan Steinegger : @Malcolm: added an example how the dynamic query could be composed. Of course it depends on the exact situation, but I think you can now build it up yourself.Malcolm : That is what I was looking for thanks. I will test it out.Malcolm : Thanks Stefan works well.Malcolm : I want to add that alot of people will get something out of this post I'm sure. -
Here is the above criteria with dynamic keywords
string searchQuery = "wine pasta"; ICriteria query = Session.CreateCriteria(typeof(Recipe), "r") .CreateCriteria("Ingredients", "i", JoinType.InnerJoin) .SetFetchMode("Images", FetchMode.Join) .SetFetchMode("Comments", FetchMode.Join) .SetFetchMode("Ingredients", FetchMode.Join) .SetResultTransformer(new DistinctRootEntityResultTransformer()); var keywords = searchQuery.Split(' '); Disjunction keywordsCriteria = Restrictions.Disjunction(); foreach (var keyword in keywords) { keywordsCriteria.Add(Restrictions.Like("i.IngredientName", string.Format("%{0}%", keyword))); keywordsCriteria.Add(Restrictions.Like("r.RecipeTitle", string.Format("%{0}%", keyword))); } query.Add(keywordsCriteria); List<Recipe> result = query.List<Recipe>(); -
Both Stefan's and Sathish's examples concatenate % operators into the SQL. This is unnecesary as Restrictions.Like (nhib 2.0+) and Expression.Like (before v2.0) have 3 parameter versions with a MatchMode.
Disjunction keywordsCriteria = Restrictions.Disjunction(); foreach (var keyword in keywords) { keywordsCriteria.Add(Restrictions.Like("i.IngredientName", keyword, MatchMode.Anywhere)); keywordsCriteria.Add(Restrictions.Like("r.RecipeTitle", keyword, MatchMode.Anywhere)); }Full text queries are also available with NHibernate Search. See Ayende's example for more details.
0 comments:
Post a Comment