• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

multiple condition in referencing collection in object using JPA

 
Darvesh Niz
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
i am using hibernate impl for JPA. i have been with this issue
i have a database table classified with have all the details about classified. There is classified_attribute table which stores the attribute_code and value_code for a specific classified
Classified_attribute Table
i have a classified object in that i have a classifiedAttribute collection. The classifiedAttribute collection has classifiedAttribute object. Each classifiedAttribute object has in turn one attribute and one value Object. The attribute object has attributeCode, attributeName etc and the value object has the valueName, valueCode etc... So a listing for 2006 Toyota Corolla will be like
Classified table will have
id subject etc..
1 2006 Toyota Corolla for sale etc...

The classified attribute tabel will have
id attribute Code value code
---------------------------------
1 A0005 34
1 A0006 42
1 A0007 44

where attributecode is a foreign key which is referenceing attribute table. so in my attribute table i have A0005 as year, A0006 as make, A0007 as model and in my value table i have value code 34 as 2006, 42 as Toyoita and 44 as corolla. Similary i have lot of listings.
The question is if i have to get all 2006 model year using this query
select classified from Classified classified, in (classified.classifiedAttributes) ca, in (ca.attribute) att,in (ca.attValue) val where ( (att.attributeCode = 'A0005' and val.valueCode in (34) )) , it works fine the issue happends wheni have to give get me all 2006 Toyotas i am using the query as
select classified from Classified classified, in (classified.classifiedAttributes) ca, in (ca.attribute) att,in (ca.attValue) val where ( (att.attributeCode = 'A0005' and val.valueCode in (34) ) and (att.attributeCode = 'A0006' and val.valueCode in (42) )
It doesn't return anything may i know what i am doing wrong, i saw the sql output which hibernate spits out, it looks wrong its making inner join

select classified0_.classified_id as classified1_3_, classified0_.address as address3_, classified0_.area_code as area8_3_, classified0_.category_id as category9_3_, classified0_.city_code as city10_3_, classified0_.description as descript3_3_, classified0_.email as email3_, classified0_.mobile as mobile3_, classified0_.phone as phone3_, classified0_.state_code as state11_3_, classified0_.subject as subject3_ from classifieds classified0_ inner join classified_attribute classified1_ on classified0_.classified_id=classified1_.classified_id inner join attribute attribute2_ on classified1_.attribute_code=attribute2_.attribute_code inner join attribute_value attributev3_ on classified1_.value_code=attributev3_.value_code where attribute2_.attribute_code='A0005' and (attributev3_.value_code in (34)) and attribute2_.attribute_code='A0006' and (attributev3_.value_code in (42))


Any help will be much appreicated.
 
Darvesh Niz
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
any body help Please!!!
Rashid
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic