-
1. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
sofie Nov 4, 2008 10:05 AM (in response to sofie)
sofie soft wrote on Nov 04, 2008 09:49:
Hello,
I've got a seam-gen generated project which (naturally) uses EntityQuery.
In my search operation I want to use the opeartionor
betwen the condition.
I want to write my guery:select m from message m where condition1 or condition2
but the restrictions in seam is treated as “and” operation.
Is it possible to makeor
between the clauses in the restriction?
Can you help me ?
please ;
can you help meI have probleme
in my projectplease help me
-
2. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
dan.j.allen Nov 5, 2008 9:59 AM (in response to sofie)You can stealthly get away with using an OR within the scope of a single restriction. The value expression in each restriction becomes a named parameter in the generated JPQL query. The name is derived by concatenating the string
el
with the 1-based index of the restriction. So the first restriction clause uses the parameter name el1 and the second el2. Here is how you would compare a value expression against two different fields.<framework:restrictions> <value> lower(golfer.lastName) like concat(lower(#{roundCriteria.golferName}),'%') or lower(golfer.firstName) like concat(lower(:el1),'%') </value> ... </framework:restrictions>
Putting that trick aside, what we still need is a way to set the operator used to combine the individual restriction clauses. I am thinking we need to add a flag to the Query component. JIRA?
-
3. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
mercury1nat Jun 12, 2009 2:26 PM (in response to sofie)I've tried to apply this trick within the EntityQuery it's self see my code below:
public class BusinessSearch extends EntityQuery<Company> { private Log log = LogFactory.getLog(this.getClass()); private static final long serialVersionUID = 2164088365294109661L; private static final String EJBQL = "from Company c"; private static final String ORDER = "name"; private static final Integer MAXRESULTS = 30; private Float lat; private Float lon; private static final String[] RESTRICTIONS = { "description like concat('%','lower(#{exampleCompany.searchPhrase})','%') or socialReturns like concat('%',':el1','%') or website like concat('%',':el1','%') ", "needsfinance = #{exampleCompany.needsfinanceFilter}", "companyType = #{exampleCompany.companyType}", "lower(replace(name,'The ','')) like lower(concat(#{exampleCompany.name},'%'))", "sector = #{exampleCompany.sector}", "c.member = #{exampleCompany.member}", "#{exampleCompany.benefitFilter} member of c.benefits", "#{exampleCompany.certificationFilter} member of c.certifications", "approved = #{constants.booleanTrue}" }; public BusinessSearch() { setEjbql(EJBQL); setOrder(ORDER); setMaxResults(MAXRESULTS); setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS)); log.info(getEjbql()); }
I'm getting the following error though:
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [el1]
-
4. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
mercury1nat Jun 12, 2009 2:28 PM (in response to sofie)Sorry for my incomplete entry above.
I meant to also say..any help or thoughts appreciated.
Thanks! Nat
-
5. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
lvdberg Jun 12, 2009 2:57 PM (in response to sofie)Bit of a side remark,
Be very carefull with OR constructs in your queries, or-constructs are not the fastest operations especially if you have a lot of entries in your DB. In case of a lot entries make sure you've indexed the columns used for searching or if you have more entries and
like-constructs
think of using Hibernate-SearchLeo
-
6. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
mercury1nat Jun 12, 2009 5:14 PM (in response to sofie)
Be very carefull with OR constructs in your queries, or-constructs are not the fastest operations especially if you have a lot of entries in your DB. In case of a lot entries make sure you've indexed the columns used for searching or if you have more entries and like-constructs think of using Hibernate-Search
LeoI totally agree with you, I would rather avoid using OR, but I am trying to implement a keyword search over multiple fields in one object and have so far not been able to implement a good solution for this or find another example that would work. Any ideas you have on this front would be most welcome!
thanks!
-
7. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
lvdberg Jun 12, 2009 5:34 PM (in response to sofie)Use an array inside your criteria
<value> yourObject IN (#{not empty criteria.arrayOfObjects ? criteria.arrayOfObjects : null}) </value>
Has the same efficiency problem, but works better.
You can use a multiValue selection as input for the arrayOfObjects and it works nice.
Works like a charm !! Not invented this myself, but you can find it in the Seam in Action book.
Darn people, spent some money, it saves you a lot of time !!
Leo
-
8. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
mercury1nat Jun 14, 2009 12:05 PM (in response to sofie)I found the problem with my code after some deliberation. The error below was misleading me into thinking that you couldn't use the el1 reference on the same line as you declare it.
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [el1]
However after some testing I have now found my issue to be a syntax mistake.
The corrected line of code is this:
"description like concat('%',lower(#{exampleCompany.searchPhrase}),'%') or socialReturns like concat('%',:el1,'%') or website like concat('%',':el1','%') ",
Basically, I had to remove the single quotes from around lower and :el1
I hope this helps someone else.