Hi everyone.
I'm in a situation that can be described like this:
I have to search the Purchases(and return the Purchases in a List), but using a ilike expression to a String in the 3rd composition of the Purchase. Is something like this:
Purchase has PurchaseItems
PurchaseItems has Product
Product has Brand
I have to search by the brand name.

Every attempt that I have brings me all the data, even when I try to search a brand name that doesn't exist! I am using the following code:

Criteria cri = getSession().createCriteria(Purchase.class);

if(!isEmpty(brandName)){
cri.createAlias("purchaseItems","purchaseItem")..setFetchMode("purchaseItem",FetchMode.EAGER)
.createAlias("product","p").createAlias("brand","b").setFetchMode("b",FetchMode.EAGER)
.add(Expression.ilike("name",brandName,MatchMode.ANYWHERE)); }

What am I missing?
PS: All the debugs placed in the web layer and in the DAO layer returns the brandName, so, the values are reaching the method.

Best Regards

Rafael Mauricio Nami

Hibernate: select order0_.order_id as order_id0_, orderdetai1_.orderdetail_id as
orderdet1_1_, performanc2_.id as id2_, production3_.production_id as producti1_
3_, order0_.time as time0_, order0_.type as type0_, order0_.cc_name as cc_name0_
, order0_.cc_type as cc_type0_, order0_.cc_number as cc_number0_, order0_.cc_exp
_date as cc_exp_d7_0_, order0_.shipping_address_line1 as shipping8_0_, order0_.s
hipping_address_line2 as shipping9_0_, order0_.shipping_address_city as shippin1
0_0_, order0_.shipping_address_state as shippin11_0_, order0_.shipping_address_z
ipcode as shippin12_0_, order0_.shipping_address_country as shippin13_0_, order0
_.name_on_ticket as name_on14_0_, order0_.total as total0_, order0_.processing_f
ee_total as process16_0_, order0_.service_fee_total as service17_0_, order0_.con
sumer_fee_total as consume18_0_, order0_.consumer_fee_total_fx as consume19_0_,
order0_.processing_fee_total_fx as process20_0_, order0_.service_fee_total_fx as
service21_0_, order0_.per_order_fee as per_ord22_0_, order0_.order_total_fx as
order_t23_0_, order0_.tracking_info as trackin24_0_, order0_.auth_code as auth_c
ode0_, order0_.resp_msg as resp_msg0_, order0_.result as result0_, order0_.pn_re
f as pn_ref0_, order0_.client_id as client_id0_, order0_.order_taker_id as order
_t30_0_, order0_.consumer_id as consume31_0_, order0_.status_id as status_id0_,
order0_.payment_type_id as payment33_0_, order0_.shipping_method_id as shippin34
_0_, order0_.shipping_carrier as shippin35_0_, orderdetai1_.type as type1_, orde
rdetai1_.price as price1_, orderdetai1_.processing_fee as processi4_1_, orderdet
ai1_.processing_fee_fx as processi5_1_, orderdetai1_.name as name1_, orderdetai1
_.description as descript7_1_, orderdetai1_.order_id as order_id1_, orderdetai1_
.performance_id as performa9_1_, orderdetai1_.service_fee as service10_1_, order
detai1_.consumer_fee as consume11_1_, orderdetai1_.price_fx as price_fx1_, order
detai1_.consumer_fee_fx as consume13_1_, orderdetai1_.service_fee_fx as service1
4_1_, orderdetai1_.group_discount_id as group_d15_1_, orderdetai1_.pct_off as pc
t_off1_, orderdetai1_.ticket_id as ticket_id1_, orderdetai1_.ticket_type_id as t
icket_18_1_, orderdetai1_.promotion_name as promoti19_1_, orderdetai1_.promotion
_code as promoti20_1_, performanc2_.version as version2_, performanc2_.perf_star
t as perf_start2_, performanc2_.perf_end as perf_end2_, performanc2_.status as s
tatus2_, performanc2_.notes as notes2_, performanc2_.production_id as producti7_
2_, production3_.version as version3_, production3_.prod_name as prod_name3_, pr
oduction3_.prod_desc as prod_desc3_, production3_.image_file_name as image_fi5_3
_, production3_.logo_file_name as logo_fil6_3_, production3_.landing_url as land
ing_7_3_, production3_.shutoff_hour as shutoff_8_3_, production3_.max_tix_allowe
d as max_tix_9_3_, production3_.notes as notes3_, production3_.boxoffice_hours a
s boxoffi11_3_, production3_.receipt_text as receipt12_3_, production3_.venue_ad
dress_line1 as venue_a13_3_, production3_.venue_address_line2 as venue_a14_3_, p
roduction3_.venue_address_city as venue_a15_3_, production3_.venue_address_state
as venue_a16_3_, production3_.venue_address_zipcode as venue_a17_3_, production
3_.venue_address_country as venue_a18_3_, production3_.venue_name as venue_name3
_, production3_.venue_location as venue_l20_3_, production3_.venue_transit_info
as venue_t21_3_, production3_.venue_phone as venue_p22_3_, production3_.ticket_p
rint_method as ticket_23_3_, production3_.seat_picking_method as seat_pi24_3_, p
roduction3_.eticket_seat_number as eticket25_3_, production3_.reserved_seating a
s reserve26_3_, production3_.status as status3_, production3_.per_order_fee_name
as per_ord28_3_, production3_.per_order_fee_amount as per_ord29_3_, production3
_.ad_banner_html as ad_bann30_3_, production3_.style as style3_, production3_.se
ating_chart_id as seating32_3_, production3_.client_id as client_id3_ from order
s order0_ inner join order_detail orderdetai1_ on order0_.order_id=orderdetai1_.
order_id inner join performance performanc2_ on orderdetai1_.performance_id=perf
ormanc2_.id inner join production production3_ on performanc2_.production_id=pro
duction3_.production_id where (production3_.prod_name like ? ) group by order0_
.order_id

Query q = null;
String hql = "from Order as o "+
"join o.orderdetails as orderDetails "+
"join orderDetails.performance as performance "+
"join performance.production as production "+
"where production.name like :name group by o";
q = getSession().createQuery(hql);
q.setString("name","%"+productionName+"%");
return q.list();

Hibernate: select this.order_id as order_id11_, this.time as time11_, this.type
as type11_, this.cc_name as cc_name11_, this.cc_type as cc_type11_, this.cc_numb
er as cc_number11_, this.cc_exp_date as cc_exp_d7_11_, this.shipping_address_lin
e1 as shipping8_11_, this.shipping_address_line2 as shipping9_11_, this.shipping
_address_city as shippin10_11_, this.shipping_address_state as shippin11_11_, th
is.shipping_address_zipcode as shippin12_11_, this.shipping_address_country as s
hippin13_11_, this.name_on_ticket as name_on14_11_, this.total as total11_, this
.processing_fee_total as process16_11_, this.service_fee_total as service17_11_,
this.consumer_fee_total as consume18_11_, this.consumer_fee_total_fx as consume
19_11_, this.processing_fee_total_fx as process20_11_, this.service_fee_total_fx
as service21_11_, this.per_order_fee as per_ord22_11_, this.order_total_fx as o
rder_t23_11_, this.tracking_info as trackin24_11_, this.auth_code as auth_code11
_, this.resp_msg as resp_msg11_, this.result as result11_, this.pn_ref as pn_ref
11_, this.client_id as client_id11_, this.order_taker_id as order_t30_11_, this.
consumer_id as consume31_11_, this.status_id as status_id11_, this.payment_type_
id as payment33_11_, this.shipping_method_id as shippin34_11_, this.shipping_car
rier as shippin35_11_, user1_.user_id as user_id0_, user1_.version as version0_,
user1_.email as email0_, user1_.password as password0_, user1_.first_name as fi
rst_name0_, user1_.last_name as last_name0_, user1_.disabled as disabled0_, user
1_.create_date as create_d8_0_, user1_.last_login_date as last_log9_0_, user1_.p
assword_hint as passwor10_0_, user1_.client_id as client_id0_, consumer2_.consum
er_id as consumer1_1_, consumer2_.phone as phone1_, consumer2_.email as email1_,
consumer2_.password as password1_, consumer2_.last_name as last_name1_, consume
r2_.first_name as first_name1_, consumer2_.cc_name as cc_name1_, consumer2_.cc_t
ype as cc_type1_, consumer2_.cc_number as cc_number1_, consumer2_.cc_exp_date as
cc_exp_10_1_, consumer2_.billing_address_line1 as billing11_1_, consumer2_.bill
ing_address_line2 as billing12_1_, consumer2_.billing_address_city as billing13_
1_, consumer2_.billing_address_state as billing14_1_, consumer2_.billing_address
_zipcode as billing15_1_, consumer2_.billing_address_country as billing16_1_, co
nsumer2_.shipping_address_line1 as shippin17_1_, consumer2_.shipping_address_lin
e2 as shippin18_1_, consumer2_.shipping_address_city as shippin19_1_, consumer2_
.shipping_address_state as shippin20_1_, consumer2_.shipping_address_zipcode as
shippin21_1_, consumer2_.shipping_address_country as shippin22_1_, consumer2_.em
ail_signup as email_s23_1_, consumer2_.create_date as create_24_1_, consumer2_.l
ast_login_date as last_lo25_1_, consumer2_.disabled as disabled1_, consumer2_.no
tes as notes1_, consumer2_.password_hint as passwor28_1_, orderstatu3_.order_sta
tus_id as order_st1_2_, orderstatu3_.version as version2_, orderstatu3_.name as
name2_, paymenttyp4_.paymenttype_id as paymentt1_3_, paymenttyp4_.version as ver
sion3_, paymenttyp4_.payment_type_name as payment_3_3_, paymenttyp4_.payment_typ
e_desc as payment_4_3_, paymenttyp4_.client_id as client_id3_, shippingme5_.prod
uction_shipping_method_id as producti1_4_, shippingme5_.version as version4_, sh
ippingme5_.name as name4_, shippingme5_.cost as cost4_, shippingme5_.start_days
as start_days4_, shippingme5_.end_days as end_days4_, shippingme5_.production_id
as producti7_4_, od.orderdetail_id as orderdet1_5_, od.type as type5_, od.price
as price5_, od.processing_fee as processi4_5_, od.processing_fee_fx as processi
5_5_, od.name as name5_, od.description as descript7_5_, od.order_id as order_id
5_, od.performance_id as performa9_5_, od.service_fee as service10_5_, od.consum
er_fee as consume11_5_, od.price_fx as price_fx5_, od.consumer_fee_fx as consume
13_5_, od.service_fee_fx as service14_5_, od.group_discount_id as group_d15_5_,
od.pct_off as pct_off5_, od.ticket_id as ticket_id5_, od.ticket_type_id as ticke
t_18_5_, od.promotion_name as promoti19_5_, od.promotion_code as promoti20_5_, p
.id as id6_, p.version as version6_, p.perf_start as perf_start6_, p.perf_end as
perf_end6_, p.status as status6_, p.notes as notes6_, p.production_id as produc
ti7_6_, prod.production_id as producti1_7_, prod.version as version7_, prod.prod
_name as prod_name7_, prod.prod_desc as prod_desc7_, prod.image_file_name as ima
ge_fi5_7_, prod.logo_file_name as logo_fil6_7_, prod.landing_url as landing_7_7_
, prod.shutoff_hour as shutoff_8_7_, prod.max_tix_allowed as max_tix_9_7_, prod.
notes as notes7_, prod.boxoffice_hours as boxoffi11_7_, prod.receipt_text as rec
eipt12_7_, prod.venue_address_line1 as venue_a13_7_, prod.venue_address_line2 as
venue_a14_7_, prod.venue_address_city as venue_a15_7_, prod.venue_address_state
as venue_a16_7_, prod.venue_address_zipcode as venue_a17_7_, prod.venue_address
_country as venue_a18_7_, prod.venue_name as venue_name7_, prod.venue_location a
s venue_l20_7_, prod.venue_transit_info as venue_t21_7_, prod.venue_phone as ven
ue_p22_7_, prod.ticket_print_method as ticket_23_7_, prod.seat_picking_method as
seat_pi24_7_, prod.eticket_seat_number as eticket25_7_, prod.reserved_seating a
s reserve26_7_, prod.status as status7_, prod.per_order_fee_name as per_ord28_7_
, prod.per_order_fee_amount as per_ord29_7_, prod.ad_banner_html as ad_bann30_7_
, prod.style as style7_, prod.seating_chart_id as seating32_7_, prod.client_id a
s client_id7_, groupdisco9_.groupdiscount_id as groupdis1_8_, groupdisco9_.versi
on as version8_, groupdisco9_.number_tix as number_tix8_, groupdisco9_.pct_off a
s pct_off8_, groupdisco9_.production_id as producti5_8_, ticket10_.ticket_id as
ticket_id9_, ticket10_.version as version9_, ticket10_.status_initial as status_
i3_9_, ticket10_.last_timestamp as last_tim4_9_, ticket10_.performance_id as per
forma5_9_, ticket10_.ticket_seat_id as ticket_s6_9_, tickettype11_.ticket_type_i
d as ticket_t1_10_, tickettype11_.version as version10_, tickettype11_.tix_type_
name as tix_type3_10_, tickettype11_.price as price10_, tickettype11_.consumer_f
ee as consumer5_10_, tickettype11_.consumer_fee_bo as consumer6_10_, tickettype1
1_.status as status10_, tickettype11_.display_order as display_8_10_, tickettype
11_.production_id as producti9_10_, tickettype11_.section_id as section_id10_ fr
om orders this left outer join user user1_ on this.order_taker_id=user1_.user_id
left outer join consumer consumer2_ on this.consumer_id=consumer2_.consumer_id
left outer join orderstatus orderstatu3_ on this.status_id=orderstatu3_.order_st
atus_id left outer join paymenttype paymenttyp4_ on this.payment_type_id=payment
typ4_.paymenttype_id left outer join production_shipping_methods shippingme5_ on
this.shipping_method_id=shippingme5_.production_shipping_method_id inner join o
rder_detail od on this.order_id=od.order_id inner join performance p on od.perfo
rmance_id=p.id inner join production prod on p.production_id=prod.production_id
left outer join groupdiscount groupdisco9_ on od.group_discount_id=groupdisco9_.
groupdiscount_id left outer join ticket ticket10_ on od.ticket_id=ticket10_.tick
et_id left outer join ticket_type tickettype11_ on od.ticket_type_id=tickettype1
1_.ticket_type_id where lower(prod.prod_name) like ?
Hibernate: select orderstatu0_.order_status_id as order_st1_, orderstatu0_.versi
on as version, orderstatu0_.name as name from orderstatus orderstatu0_ order by
orderstatu0_.name desc

Criteria cri = getSession().createCriteria(Order.class);
cri.createAlias("orderdetails","od").setFetchMode("od",FetchMode.EAGER)
.createAlias("od.performance","p").createAlias("p.production","prod")
.add(Expression.ilike("prod.name",productionName,MatchMode.ANYWHERE));
return cri.list();