I have an unwanted join in my generated sql query. I am using TopLink 10.1.3.
Say I have two objects, associated by inheritence. Artist is the superclass of Singer. They use a common ARTISTID pk, and there is a classindicator field in Artist.
When using a Singer in a where clause, the query unnecessarily joins Artist (or Singer, depending on your perspective). For example, say Song is associated with Singer, if I create a ReadAllQuery for all Songs associated with a particular Singer, then the query looks something like this:
SELECT t0.SONGID, t0.TITLE FROM SINGER t2, ARTIST t1, SONG t0 WHERE t1.ARTISTID = 100 AND t2.ARTISTID = t0.ARTISTID AND t2.ARTISTID = t1.ARTISTID AND t1.CLASSINDICATOR = 1;
The fully optimized version of this query is this:
SELECT t0.SONGID, t0.TITLE FROM SONG t0 WHERE t0.ARTISTID = 100;
The Artist join could be removed:
SELECT t0.SONGID, t0.TITLE FROM SINGER t2, SONG t0 WHERE t2.ARTISTID = 100 AND t2.ARTISTID = t0.ARTISTID;
The Singer join could be removed:
SELECT t0.SONGID, t0.TITLE FROM ARTIST t1, SONG t0 WHERE t1.ARTISTID = 100 AND t1.ARTISTID = t0.ARTISTID AND t1.CLASSINDICATOR = 1;
I can get to the fully optimized version by changing the WHERE clause to compare the ARTISTID fields/attributes, instead of the objects themselves, but this fails when chaining in additional objects. For example, if I had a fourth object Lyric, associated with Song, and I queried all Lyrics associated with a Singer, then I am relying on the descriptor for the relationships and cannot access the fields/attributes meaningfully.
So I am wondering how to get to either the third or fourth version of this query, eliminating at least one of the unnecessary joins.
I believe enhancements were made that allow queries over relationships to just use the foreign key value rather than force a table join, but I am not sure of what version this was put in - you may have to use TopLink 12/EclipseLink. You should move to a later version if possible anyway, as I do not know how long TopLink 10.1.3 is supported for.
That said, as long as there is a join to Singer, both Singer and Artist tables will be pulled in. If you cannot upgrade to a version that can optimize out the join, the best way forward is to add a query key for the ARTISTID foreign key in Song and any other descriptors that may reference the Artist table. Query keys act just like basic mappings without requiring an attribute in your object. More information and examples can be found following the links from here: