Hello,
Here is my Criteria based multi join query. It works fine, except that I would like it to return only the mail properties and not the admin ones. I guess I need some projections to do that...
Code:
public List<Mail> findLikePattern(String pattern) {
pattern = "%" + pattern + "%";
Criterion description = Restrictions.ilike("description", pattern);
Criterion subject = Restrictions.ilike("subject", pattern);
Criterion body = Restrictions.ilike("body", pattern);
Criterion firstname = Restrictions.ilike("a.firstname", pattern);
Criterion lastname = Restrictions.ilike("a.lastname", pattern);
Criterion login = Restrictions.ilike("a.login", pattern);
Criterion email = Restrictions.ilike("a.email", pattern);
Criterion name = Restrictions.ilike("c.name", pattern);
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(description).add(subject).add(body).add(firstname).add(lastname).add(login).add(email).add(name);
Criteria criteria = getSession().createCriteria(Mail.class);
criteria.add(disjunction).addOrder(Order.asc("subject")).createAlias("admin", "a", Criteria.LEFT_JOIN).createAlias("mailCategory", "c", Criteria.LEFT_JOIN);
return criteria.list();
}
Instead of
Quote:
select
this_.id as id57_2_,
this_.version as version57_2_,
this_.description as descript3_57_2_,
this_.subject as subject57_2_,
this_.body as body57_2_,
this_.text_format as text6_57_2_,
this_.attachments as attachme7_57_2_,
this_.creation_datetime as creation8_57_2_,
this_.send_datetime as send9_57_2_,
this_.admin_id as admin10_57_2_,
this_.category_id as category11_57_2_,
a1_.id as id27_0_,
a1_.version as version27_0_,
a1_.login as login27_0_,
a1_.password as password27_0_,
a1_.password_salt as password5_27_0_,
a1_.firstname as firstname27_0_,
a1_.lastname as lastname27_0_,
a1_.email as email27_0_,
a1_.super_admin as super9_27_0_,
a1_.preference_admin as preference10_27_0_,
a1_.address as address27_0_,
a1_.zip_code as zip12_27_0_,
a1_.city as city27_0_,
a1_.country as country27_0_,
a1_.profile as profile27_0_,
c2_.id as id50_1_,
c2_.version as version50_1_,
c2_.name as name50_1_,
c2_.description as descript4_50_1_
from
mail this_
left outer join
admin a1_
on this_.admin_id=a1_.id
left outer join
mail_category c2_
on this_.category_id=c2_.id
where
(
lower(this_.description) like ?
or lower(this_.subject) like ?
or lower(this_.body) like ?
or lower(a1_.firstname) like ?
or lower(a1_.lastname) like ?
or lower(a1_.login) like ?
or lower(a1_.email) like ?
or lower(c2_.name) like ?
)
order by
this_.subject asc
I would like to have
Quote:
select
this_.id as id57_2_,
this_.version as version57_2_,
this_.description as descript3_57_2_,
this_.subject as subject57_2_,
this_.body as body57_2_,
this_.text_format as text6_57_2_,
this_.attachments as attachme7_57_2_,
this_.creation_datetime as creation8_57_2_,
this_.send_datetime as send9_57_2_,
this_.admin_id as admin10_57_2_,
this_.category_id as category11_57_2_,
from
mail this_
left outer join
admin a1_
on this_.admin_id=a1_.id
left outer join
mail_category c2_
on this_.category_id=c2_.id
where
(
lower(this_.description) like ?
or lower(this_.subject) like ?
or lower(this_.body) like ?
or lower(a1_.firstname) like ?
or lower(a1_.lastname) like ?
or lower(a1_.login) like ?
or lower(a1_.email) like ?
or lower(c2_.name) like ?
)
order by
this_.subject asc
Hard to find any solution any where.. tough little nut.
Login
FAQ
Search
