I tried to reproduce this SQL query which is working for me, havent copied exact syntax for privacy issues, but I think you get the purpose from this query.
SELECT t1.Column1, t2.Column2, t2.Column3
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.Column1 LIKE %t2.Column2%
INNER JOIN Table2 as t2 ON t1.Column1 LIKE %t2.Column3% ;
while on the HQL query I tried this but it doesnt seem to get me any results :
returns empty list [ ]
HQL query :
session.createQuery("... where
t1.Column1 LIKE '%"+"t2.Column2"+"%'
and t1.Column1 LIKE '%"+"t2.Column3"+"%' ").list();
I've tried to insert actual values instead of columns, it does actually work, but i think hibernate treats attributes as raw values instead of iterating over the objects. Can anyone please suggest a solution , I've seen people approaching this issue with criterias, but the main problem is that I'm not making any associations between these classes so I don't think it'd work.
I've been stuck with this problem for over a week now, one very easy solution that comes to my mind but it's not really optimal, is doing a for each loop like this one (keeping it as last resolve):
//list1 of query result unique column2 values
//list2 of query result unique column3 values
for each (list1)
for each (list2)
HashSet<Object,Object> result = Session.createQuery("... where
t1.Column1 LIKE '%"+":list1"+"%'
and t1.Column1 LIKE '%"+":list2"+"%'").setParameter("list1",list1).setParameter("list2",list2).tolist();
HashSet<Object,Object> lastresult= new HashSet();
lastresult.add(result);
Update (Solution) :
Update : As suspected, the query was treating the object's properties as raw values instead of actual objects to iterate from, so the approach I took by adding "+" sign was the mistake, I should've gone with the function 'CONCAT' instead and it did resolve my issue.
session.createQuery("... WHERE lower(t1.Column1) LIKE
Concat(Concat('%',lower(t2.Column2)),'%') and lower(t1.Column1) LIKE Concat(Concat('%',lower(t2.Column3)),'%')").tolist();