CODEWITHSUNDEEP
sqlhibernatehibernate-criteria
Spooky
Spooky

Reputation: 65

Hibernate criteria query for join with condition

I have the following SQL query:

SELECT
    tos.ID
FROM
    TESTCYCLE_OPCO_SETTING tos
        JOIN OPCO o
            ON o.ID = tos.OPCO_ID
        JOIN TESTCYCLE tc
            ON tc.ID = tos.TESTCYCLE_ID
        LEFT OUTER JOIN BUILD b
            ON b.ID = tc.BUILD_ID
        LEFT OUTER JOIN BUILD_OPCO_SETTING bos
            ON bos.BUILD_ID = b.ID
            AND bos.OPCO_ID = o.ID

What is needed is that the OPCO_ID form the TESTCYCLE_OPCO_SETTING is used to fetch the corresponding BUILD_OPCO_SETTING. I have done this with the "AND" condition in the last JOIN.

I now want to create a Hibernate Critera query out of this and created the following aliases for the joins:

criteria.createAlias("opco", "opco");    
criteria.createAlias("testcycle", "testcycle");
criteria.createAlias("testcycle.build", "build", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("build.buildOpcoSettings", "buildOpcoSetting", CriteriaSpecification.LEFT_JOIN, Restrictions.eqProperty("buildOpcoSetting.opco.id", "opco.id"));

However the 4th alias throws an SQL error and I think I'm using this incorrectly for what I want to achieve. If I replace the property "opco.id" with a specific numeric value it works but this doesn't help me of course. Any ideas?

Upvotes: 1

Views: 2888

Answers (1)

Spooky
Spooky

Reputation: 65

Found the solution myself. Using the "opco" alias within the "buildOpcoSetting" alias lead to a wrong order of joins in the resulting SQL and an extra "opco" join. Replaced it with the opco reference from the "testcycleOpcoSetting" which works fine.

criteria.createAlias("build.buildOpcoSettings", "buildOpcoSetting", CriteriaSpecification.LEFT_JOIN,Restrictions.eqProperty("buildOpcoSetting.opco.id", "testcycleOpcoSetting.opco.id"));

Upvotes: 1

Related Questions