criteria

Howto create ICreteria when 2 subclasses have same property name?

Hi all, When having the following model: class Organisation { } class Insurance : Organisation { public int UZOVInumber {get;set;) ... } class HealthcareOffice : Organisation { public int UZOVInumber {get;set;) ... } class OtherOrganisation : Organisation { ... } You can see that 2 subclasses have the same prop...

how to translate this query into Criteria ??

Hi, I try to translate this query into Criteria (with Propel), but without success. Can you help me please ? SELECT DISTINCT (email) FROM user, travail WHERE travail.id_user = user.id_user AND id_site = "1" AND `droits` = "1" This my Criteria query : $c = new Criteria(); $c->add(self::DROITS, 1, Criteria::EQUAL); $c->add(Trav...

Sorting two columns using NHibernate Criteria API

I need to sort a result set by two columns, one of which is always NULL. A simplified view of Database where the columns I wish to sort on look like : ColumnA is from Table A Column B, B1 is from table B ColumnA ColumnB ColumnB1 U NULL NULL P NULL NULL L NULL NULL NULL U NULL NULL ...

Prevent NHibernate from aliasing in ORDER BY

I have a query which has an Order By clause. The generated SQL from NHibernate looks like ORDER BY coalesce(x.Company as x__.Company, y.Company) asc This fails as 'as' is not allowed in Order by clause in MS SQL Server. Is there any way I can prevent aliasing? The criteria query that I have written looks like: var orderBy = Proj...

Hibernate Criteria - Exclude groupProperty from select

I would like to use a hibernate criteria object as a subquery on a second criteria, like this: DetachedCriteria latestStatusSubquery = DetachedCriteria.forClass(BatchStatus.class); latestStatusSubquery.setProjection(Projections.projectionList() .add( Projections.max("created"), "latestStatusDate") .add( P...

NHibernate "SELECT ... FROM (SELECT ..." Criteria

I have a SQL query I'm having trouble creating using NHibernate Criteria: SELECT ID, COLA, COLB, COLC FROM table WHERE COLC='something' AND ID IN (SELECT ID FROM (SELECT MAX(ID) as ID, COLA FROM table WHERE COLB='something' GROUP BY COLA) subquery) ORDER BY ID DESC I originally had this slightly simpler query: SELECT ID, COLA, COLB, ...

Distinct elements using NHibernate ICriteria for pagination

I am implementing a pagination solution using NHibernate ICriteria where I wish to display distinct elements within the page. The ICriteria for this is: Session.CreateCriteria(typeof(Employee), "a") .CreateCriteria("Company", "b") //BUNCH OF JOINS .SetProjection(Projections.ProjectionList() ...

Criteria for SELECT bla1, bla2 FROM foo

I want a criteria that only selects bla1 and bla2 from all attributes of a given domain Foo.findAll() or def c = Foo.createCriteria() def results = c {} have: SELECT * FROM foo results = List of all attributes of given domain foo want SELECT bla1,bla2 FROM foo written as Criteria def c = Foo.createCriteria() def results = c {...

Passing a propel criteria to the symfony routing function that retrieves the object

Hi guys, quick symfony / propel question. I have the following propel collection route: api_offer: class: sfPropelRouteCollection options: prefix_path: /api/offer model: Offer plural: offers singluar: offer actions: [ list ] module: apiOffer requirements: sf_format: (?:html|json) My question is, does ...

NHibernate: Querying with OR operator

I am looking for a way to build an OR operator into a query to look for a particular value in one field of a table as well as in another field of a joined table. This is pretty basic in SQL, but I can't for the world figure out how to do this in NHibernate. I have been searching the web, but the examples I find are pretty nebulous to me ...

Criteria Many-to-Many Hibernate

@Entity public class Person implements Serializable { private int id; ........... private Set<Languages> languages = new HashSet<Languages>(); ............... @ManyToMany @JoinTable(name = "link_person_languages") public Set<Languages> getLanguages() { return languages; } } @Entity public...

Complicated query with hibernate criteria.

Hello, I want to get QuestionVersion with max versionNumber for each Question. Next I need to check status of those versions and get QuestionVersionContents associated with versions with correct status value. Following statement seems to work but I need It as criteria or at least HQL: select c.content, c.language_id, qv.status, qv.ver...

Hibernate - Perform HQL query on Criteria Result

Hi @ all, I am concerned with a problem regarding criteria and hql. In first step a complex criteria construction limits a big amount of datasets. In second step a hql calculation should be performed on these preselected datasets of step one. The problem is that the code of both has been developed seperately and i am wondering if it is...

Using groupProperty and countDistinct in Grails Criteria

I'm using Grails 1.2.4. I would like to know on how can I sort by "countDistinct" (descending) and with groupProperty inside a projections. Here are my domains: class Transaction { static belongsTo = [ customer : Customer, product : Product ] Date transactionDate = new Date() static constraints = { transactionDat...

Using CONTAINS from HQL / Criteria API

Hi all, I'm using NHibernate 2.1.2.4000GA. I'm trying to use SQL Server's CONTAINS function from within HQL and the criteria APIs. This works fine in HQL: CONTAINS(:value) However, I need to qualify the table in question. This works fine: CONTAINS(table.Column, :value) However, I need to search across all indexed columns in my tab...

Hibernate - Criteria - bidirectional search with unidirectional mapping?

Could you tell me is possible to search by hibernate criteria on bidirectional direction with only unidirectional associations? For examle: I have two class: Box { Long id; Set parcels; Integer status; // other properites } Parcel { Long id; // other properties } I've got associations one-to-many in cl...

Setting fetch mode via criteria several levels deep in object graph.

I have a situation where entity A contains a set of entity B. Each entity B contains one entity C. Each entity C has a list of entity D's. I want all A's that meet some criteria but I also want the D's eagerly loaded (mapping file lazily loads them). I have code similar to the following Criteria aCriteria = session.createCriteria(A.clas...

Is it possible to remove order from Hibernate Criteria?

If I have an @OrderBy("someProperty") annotation on an object and then use a Criteria to add an ORDER BY clause like so: criteria.addOrder(Order.asc("id")); The resulting SQL will do the ordering like this: ORDER BY someProperty, id asc Is it possible to change the order of the two or to remove the someProperty order? I can't r...

org.hibernate.QueryException: Not all named parameters have been set

Hi all, I'm getting extremely weird behavior out of JPA 2.0 Criteria API with Hibernate 3.5.1-Final as a provider. I'm trying to build a dynamic query that looks like this in JPQL: SELECT e FROM Employee e WHERE lower(e.firstName) like lower(:employeeName) OR lower(e.lastName) like lower(:employeeName) but keep getting the er...

Hibernate 2, Using Criteria to order by associated property

I've been able to successfully do this with Hibernate3, but cannot find the corresponding syntax in Hibernate2, and am wondering if it's even possible. Criteria crit = session.createCriteria(User.class). createAlias("organization", "organization"). addOrder(Order.asc("name")); Hibernate2 doesn't support FetchMode.JOIN, and the abo...