EclipseLink Many-to-Many query problems with MySql

[sonavor]

First of all, I had originally posted a question about using "IN" with a JPA query where I have two tables joined by a mapping table.  For some reason that posting no longer works this forum so I will re-state that basic problem.

Using a table for media called MEDIA_LISTING I have a second table called KEYWORDS. I didn't want to search for MEDIA_LISTING records using a text search for keywords that are associated so I created a mapping table called KW_MAP.

The table structures are as follows -

MEDIA_LISTING
MEDIA_ID   integer  PK
MEDIA_TITLE  varchar(50)
...other columns...

KEYWORDS
KW_ID  integer  PK
KW_NAME varchar(50)

KW_MAP
KW_ID       integer  PK
MEDIA_ID  integer   PK   ... PK comprised of both values

In my original posting I was having a problem determining the best way to have a query where I could find MEDIA_LISTING items by keywords.  I wanted to search for MEDIA_LISTING records where the MediaListing JPA entity's keywords Collection was "IN" a submitted Collection of keywords (named kwCollection in my case).

The solution I ended up with was to use EclipseLink's ExpressionBuilder and join the keyword table in the MediaListing query like this -

List<MediaListing> mList = new ArrayList<MediaListing>();
ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);

Expression expKw = eb.anyOf("kwCollection").in(kwList);

I then construct the query -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.setIsReadOnly(true);
readAllQuery.prepareForExecution();

...and execute it.

That all works fine.  However, after testing it for a while and increasing my test data size I ran into a problem where I found there is a limit to the number of keywords that can be used in the query.  

This is due to the resulting SQL that the ReadAllQuery generates from the Expression created by the ExpressionBuilder.

A small query for the MediaListing entity where I am searching for two keywords results in this SQL:

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, KW_MAP t3, KEYWORD t2, MEDIA_LISTING t1, KEYWORD t0
WHERE (((t0.KW_ID = ?) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID = t1.MEDIA_ID) AND (t0.KW_ID = t3.KW_ID))
AND ((t4.MEDIA_ID = t1.MEDIA_ID) AND (t2.KW_ID = t4.KW_ID)))) LIMIT ?, ?
bind => [405, 406, 0, 10]

Notice that for each keyword included in the query that two tables are joined - a KW_MAP join and a KEYWORD join.  There is a limit in MySql of 61 tables in a join.  When you factor in adding other MEDIA_LISTING columns to the query that eats up a lot of the query joins that available.

If I create a SQL statement myself to do the query I can do this -

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, MEDIA_LISTING t1 WHERE (t1.`MEDIA_ID` = t4.`MEDIA_ID`) AND
(t4.`KW_ID` IN (405, 406))

I understand that this query works because I am not including any t4 (KW_MAP) columns in the SELECT portion of the SQL.

My question is -
Is the solution for this query Join table limit to use a custom, native MySql query instead of the generated query that EclipseLink creates?

Thanks,
Sonavor

[James Sutherland]

The SQL generate does not make sense, perhaps log a bug for this.

Your collection seems to be keyword objects, note the strings or ids.  You could convert the collection to the ids, or string and do,

Expression expKw = eb.anyOf("kwCollection").get("keyword").in(keywordStringList);

Also you had,
readAllQuery.setSelectionCriteria(exp);

I assume you meant expKw?

Also only set firstResult if you are using pagination, don't set it to 0.
readAllQuery.setFirstResult(0);

and don't call,
readAllQuery.prepareForExecution();

this is an internal method, and may be causing your issue.

sonavor wrote:

First of all, I had originally posted a question about using "IN" with a JPA query where I have two tables joined by a mapping table.  For some reason that posting no longer works this forum so I will re-state that basic problem.

Using a table for media called MEDIA_LISTING I have a second table called KEYWORDS. I didn't want to search for MEDIA_LISTING records using a text search for keywords that are associated so I created a mapping table called KW_MAP.

The table structures are as follows -

MEDIA_LISTING
MEDIA_ID   integer  PK
MEDIA_TITLE  varchar(50)
...other columns...

KEYWORDS
KW_ID  integer  PK
KW_NAME varchar(50)

KW_MAP
KW_ID       integer  PK
MEDIA_ID  integer   PK   ... PK comprised of both values

In my original posting I was having a problem determining the best way to have a query where I could find MEDIA_LISTING items by keywords.  I wanted to search for MEDIA_LISTING records where the MediaListing JPA entity's keywords Collection was "IN" a submitted Collection of keywords (named kwCollection in my case).

The solution I ended up with was to use EclipseLink's ExpressionBuilder and join the keyword table in the MediaListing query like this -

List<MediaListing> mList = new ArrayList<MediaListing>();
ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);

Expression expKw = eb.anyOf("kwCollection").in(kwList);

I then construct the query -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.setIsReadOnly(true);
readAllQuery.prepareForExecution();

...and execute it.

That all works fine.  However, after testing it for a while and increasing my test data size I ran into a problem where I found there is a limit to the number of keywords that can be used in the query.  

This is due to the resulting SQL that the ReadAllQuery generates from the Expression created by the ExpressionBuilder.

A small query for the MediaListing entity where I am searching for two keywords results in this SQL:

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, KW_MAP t3, KEYWORD t2, MEDIA_LISTING t1, KEYWORD t0
WHERE (((t0.KW_ID = ?) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID = t1.MEDIA_ID) AND (t0.KW_ID = t3.KW_ID))
AND ((t4.MEDIA_ID = t1.MEDIA_ID) AND (t2.KW_ID = t4.KW_ID)))) LIMIT ?, ?
bind => [405, 406, 0, 10]

Notice that for each keyword included in the query that two tables are joined - a KW_MAP join and a KEYWORD join.  There is a limit in MySql of 61 tables in a join.  When you factor in adding other MEDIA_LISTING columns to the query that eats up a lot of the query joins that available.

If I create a SQL statement myself to do the query I can do this -

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, MEDIA_LISTING t1 WHERE (t1.`MEDIA_ID` = t4.`MEDIA_ID`) AND
(t4.`KW_ID` IN (405, 406))

I understand that this query works because I am not including any t4 (KW_MAP) columns in the SELECT portion of the SQL.

My question is -
Is the solution for this query Join table limit to use a custom, native MySql query instead of the generated query that EclipseLink creates?

Thanks,
Sonavor

[sonavor]

Yes, sorry about the confusion on the expression.  I only showed the keyword part of my expression building code.  There is an overall Expression "exp" variable that I am constructing and the expKw is added to it.

Getting the keyword name from the keyword object and checking it against a keyword String list sounds like it could work.  I just think it would be slower than having the query comparing numeric primary keys.  Currently, in my search I have the keywords for the search entry selected from a multi-select list.  However, later I will probably be implementing a user-entered keyword search section where keywords are typed in so the String list compare would be better in that case.

Removing the prepareForExecution() and setFirstResult(0) didn't affect the SQL that gets generated.

If this is a bug in the way EclipseLink generates the SQL what is the procedure for logging a bug (I've never done that before).

-sonavor



The SQL generate does not make sense, perhaps log a bug for this.

Your collection seems to be keyword objects, note the strings or ids.  You could convert the collection to the ids, or string and do,

Expression expKw = eb.anyOf("kwCollection").get("keyword").in(keywordStringList);

Also you had,
readAllQuery.setSelectionCriteria(exp);

I assume you meant expKw?

Also only set firstResult if you are using pagination, don't set it to 0.
readAllQuery.setFirstResult(0);

and don't call,
readAllQuery.prepareForExecution();

this is an internal method, and may be causing your issue.

sonavor wrote:

First of all, I had originally posted a question about using "IN" with a JPA query where I have two tables joined by a mapping table.  For some reason that posting no longer works this forum so I will re-state that basic problem.

Using a table for media called MEDIA_LISTING I have a second table called KEYWORDS. I didn't want to search for MEDIA_LISTING records using a text search for keywords that are associated so I created a mapping table called KW_MAP.

The table structures are as follows -

MEDIA_LISTING
MEDIA_ID   integer  PK
MEDIA_TITLE  varchar(50)
...other columns...

KEYWORDS
KW_ID  integer  PK
KW_NAME varchar(50)

KW_MAP
KW_ID       integer  PK
MEDIA_ID  integer   PK   ... PK comprised of both values

In my original posting I was having a problem determining the best way to have a query where I could find MEDIA_LISTING items by keywords.  I wanted to search for MEDIA_LISTING records where the MediaListing JPA entity's keywords Collection was "IN" a submitted Collection of keywords (named kwCollection in my case).

The solution I ended up with was to use EclipseLink's ExpressionBuilder and join the keyword table in the MediaListing query like this -

List<MediaListing> mList = new ArrayList<MediaListing>();
ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);

Expression expKw = eb.anyOf("kwCollection").in(kwList);

I then construct the query -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.setIsReadOnly(true);
readAllQuery.prepareForExecution();

...and execute it.

That all works fine.  However, after testing it for a while and increasing my test data size I ran into a problem where I found there is a limit to the number of keywords that can be used in the query.  

This is due to the resulting SQL that the ReadAllQuery generates from the Expression created by the ExpressionBuilder.

A small query for the MediaListing entity where I am searching for two keywords results in this SQL:

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, KW_MAP t3, KEYWORD t2, MEDIA_LISTING t1, KEYWORD t0
WHERE (((t0.KW_ID = ?) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID = t1.MEDIA_ID) AND (t0.KW_ID = t3.KW_ID))
AND ((t4.MEDIA_ID = t1.MEDIA_ID) AND (t2.KW_ID = t4.KW_ID)))) LIMIT ?, ?
bind => [405, 406, 0, 10]

Notice that for each keyword included in the query that two tables are joined - a KW_MAP join and a KEYWORD join.  There is a limit in MySql of 61 tables in a join.  When you factor in adding other MEDIA_LISTING columns to the query that eats up a lot of the query joins that available.

If I create a SQL statement myself to do the query I can do this -

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, MEDIA_LISTING t1 WHERE (t1.`MEDIA_ID` = t4.`MEDIA_ID`) AND
(t4.`KW_ID` IN (405, 406))

I understand that this query works because I am not including any t4 (KW_MAP) columns in the SELECT portion of the SQL.

My question is -
Is the solution for this query Join table limit to use a custom, native MySql query instead of the generated query that EclipseLink creates?

Thanks,
Sonavor

[sonavor]

I tried out the query expression -

Expression expKw = eb.anyOf("kwCollection").get("kwName").in(keywordStringList);

where the keywordStringList is a Collection<String> of the submitted keywords to search on.
"kwCollection" is the many-to-many relationship of MediaListing keywords ( a Collection<Keywords> ).
"kwName" is the String keyword value.

Using that expression works and results in returning a list of MediaListing JPA entities that contain at least on keyword that is in the submitted keywordStringList.

In addition, that generated SQL only joins the KEYWORDS table and the KW_MAP table once.  This partially solves my problem.

I did realize while testing this implementation that I had a mistake in my posting of the original query expression.

Previously I stated that this expression -
Expression expKw = eb.anyOf("kwCollection").in(kwList);

was what I was using and that it worked.  It actually does not work and wasn't the current code I had been trying.  When I execute that type of expression I get the following error -

Exception [EclipseLink-6075] (Eclipse Persistence Services - 1.1.2.v20090612-r4475): org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only use the equal() or notEqual() operators.  Other comparisons must be done through query keys or direct attribute level comparisons.
Expression: [
Relation operator  IN
   Query Key kwCollection
      Base com.cim.jpa.MediaListing
   Constant [com.cim.jpa.Keywords[kwId=405, kwName=coconut], com.cim.jpa.Keywords[kwId=406, kwName=coffee]]]
Query: ReportQuery(referenceClass=MediaListing )

The "in" operator for the "anOf" expression must compare objects using an equal or notEqual operator.

So the solution of using
Expression expKw = eb.anyOf("kwCollection").get("kwName").in(keywordStringList);
is the way to go.

Or I could use the Id values -
Expression expKw = eb.anyOf("kwCollection").get("kwId").in(keywordIdList);

where the keywordIdList is a Collection<Integer>

The query expression that I had working where I encountered the MySql table join limit was where I was attempting to "AND" the keywords in the join.  I would like to create a query where the MEDIA_LISTING records that contained all of the keywords submitted in a list are returned.

My attempt to do that with the ExpressionBuilder was like this -

ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);
Expression exp = null;
Collection<Keywords> kwList = searchMedia.getKwCollection();
if (kwList != null && kwList.size() > 0) {
    for (Iterator it = kwList.iterator(); it.hasNext();) {
       KwList kw = (KwList) it.next();
       Expression expKw = eb.anyOf("kwCollection").equal(kw);
       if (exp == null) {
          exp = expKw;
       } else {
          exp = exp.and(expKw);
       }
    }
}

The result of a query constructed that way is for each iteration of the submitted keyword list a new join of the KEYWORDS and KW_MAP table is added.  But that will eventually hit the MySql 61 table join limit.

Now that I think about it, that makes sense due to the repeated adding of a new keyword expression during the iteration loop.

That leaves me with a question on how to perform the keyword "AND" search without exceeding the maximum MySql table joins?

-sonavor








Yes, sorry about the confusion on the expression.  I only showed the keyword part of my expression building code.  There is an overall Expression "exp" variable that I am constructing and the expKw is added to it.

Getting the keyword name from the keyword object and checking it against a keyword String list sounds like it could work.  I just think it would be slower than having the query comparing numeric primary keys.  Currently, in my search I have the keywords for the search entry selected from a multi-select list.  However, later I will probably be implementing a user-entered keyword search section where keywords are typed in so the String list compare would be better in that case.

Removing the prepareForExecution() and setFirstResult(0) didn't affect the SQL that gets generated.

If this is a bug in the way EclipseLink generates the SQL what is the procedure for logging a bug (I've never done that before).

-sonavor

James Sutherland wrote:

The SQL generate does not make sense, perhaps log a bug for this.

Your collection seems to be keyword objects, note the strings or ids.  You could convert the collection to the ids, or string and do,

Expression expKw = eb.anyOf("kwCollection").get("keyword").in(keywordStringList);

Also you had,
readAllQuery.setSelectionCriteria(exp);

I assume you meant expKw?

Also only set firstResult if you are using pagination, don't set it to 0.
readAllQuery.setFirstResult(0);

and don't call,
readAllQuery.prepareForExecution();

this is an internal method, and may be causing your issue.

sonavor wrote:

First of all, I had originally posted a question about using "IN" with a JPA query where I have two tables joined by a mapping table.  For some reason that posting no longer works this forum so I will re-state that basic problem.

Using a table for media called MEDIA_LISTING I have a second table called KEYWORDS. I didn't want to search for MEDIA_LISTING records using a text search for keywords that are associated so I created a mapping table called KW_MAP.

The table structures are as follows -

MEDIA_LISTING
MEDIA_ID   integer  PK
MEDIA_TITLE  varchar(50)
...other columns...

KEYWORDS
KW_ID  integer  PK
KW_NAME varchar(50)

KW_MAP
KW_ID       integer  PK
MEDIA_ID  integer   PK   ... PK comprised of both values

In my original posting I was having a problem determining the best way to have a query where I could find MEDIA_LISTING items by keywords.  I wanted to search for MEDIA_LISTING records where the MediaListing JPA entity's keywords Collection was "IN" a submitted Collection of keywords (named kwCollection in my case).

The solution I ended up with was to use EclipseLink's ExpressionBuilder and join the keyword table in the MediaListing query like this -

List<MediaListing> mList = new ArrayList<MediaListing>();
ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);

Expression expKw = eb.anyOf("kwCollection").in(kwList);

I then construct the query -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.setIsReadOnly(true);
readAllQuery.prepareForExecution();

...and execute it.

That all works fine.  However, after testing it for a while and increasing my test data size I ran into a problem where I found there is a limit to the number of keywords that can be used in the query.  

This is due to the resulting SQL that the ReadAllQuery generates from the Expression created by the ExpressionBuilder.

A small query for the MediaListing entity where I am searching for two keywords results in this SQL:

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, KW_MAP t3, KEYWORD t2, MEDIA_LISTING t1, KEYWORD t0
WHERE (((t0.KW_ID = ?) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID = t1.MEDIA_ID) AND (t0.KW_ID = t3.KW_ID))
AND ((t4.MEDIA_ID = t1.MEDIA_ID) AND (t2.KW_ID = t4.KW_ID)))) LIMIT ?, ?
bind => [405, 406, 0, 10]

Notice that for each keyword included in the query that two tables are joined - a KW_MAP join and a KEYWORD join.  There is a limit in MySql of 61 tables in a join.  When you factor in adding other MEDIA_LISTING columns to the query that eats up a lot of the query joins that available.

If I create a SQL statement myself to do the query I can do this -

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, MEDIA_LISTING t1 WHERE (t1.`MEDIA_ID` = t4.`MEDIA_ID`) AND
(t4.`KW_ID` IN (405, 406))

I understand that this query works because I am not including any t4 (KW_MAP) columns in the SELECT portion of the SQL.

My question is -
Is the solution for this query Join table limit to use a custom, native MySql query instead of the generated query that EclipseLink creates?

Thanks,
Sonavor

[James Sutherland]

Using the kwName and in sounds like the best way to go.

Your issue in your code was the anyOf represents a new object (and join) every time it is called.  To have only join once you only call it once and use a variable.

Collection<Keywords> kwList = searchMedia.getKwCollection();
if (kwList != null && kwList.size() > 0) {
    Expression kwCollection = eb.anyOf("kwCollection"); // ** store variable
    for (Iterator it = kwList.iterator(); it.hasNext();) {
       KwList kw = (KwList) it.next();
       Expression expKw = kwCollection.equal(kw);  // ** use same variable
       if (exp == null) {
          exp = expKw;
       } else {
          exp = exp.or(expKw); // ** use OR
       }
    }
}


I tried out the query expression -

Expression expKw = eb.anyOf("kwCollection").get("kwName").in(keywordStringList);

where the keywordStringList is a Collection<String> of the submitted keywords to search on.
"kwCollection" is the many-to-many relationship of MediaListing keywords ( a Collection<Keywords> ).
"kwName" is the String keyword value.

Using that expression works and results in returning a list of MediaListing JPA entities that contain at least on keyword that is in the submitted keywordStringList.

In addition, that generated SQL only joins the KEYWORDS table and the KW_MAP table once.  This partially solves my problem.

I did realize while testing this implementation that I had a mistake in my posting of the original query expression.

Previously I stated that this expression -
Expression expKw = eb.anyOf("kwCollection").in(kwList);

was what I was using and that it worked.  It actually does not work and wasn't the current code I had been trying.  When I execute that type of expression I get the following error -

Exception [EclipseLink-6075] (Eclipse Persistence Services - 1.1.2.v20090612-r4475): org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only use the equal() or notEqual() operators.  Other comparisons must be done through query keys or direct attribute level comparisons.
Expression: [
Relation operator  IN
   Query Key kwCollection
      Base com.cim.jpa.MediaListing
   Constant [com.cim.jpa.Keywords[kwId=405, kwName=coconut], com.cim.jpa.Keywords[kwId=406, kwName=coffee]]]
Query: ReportQuery(referenceClass=MediaListing )

The "in" operator for the "anOf" expression must compare objects using an equal or notEqual operator.

So the solution of using
Expression expKw = eb.anyOf("kwCollection").get("kwName").in(keywordStringList);
is the way to go.

Or I could use the Id values -
Expression expKw = eb.anyOf("kwCollection").get("kwId").in(keywordIdList);

where the keywordIdList is a Collection<Integer>

The query expression that I had working where I encountered the MySql table join limit was where I was attempting to "AND" the keywords in the join.  I would like to create a query where the MEDIA_LISTING records that contained all of the keywords submitted in a list are returned.

My attempt to do that with the ExpressionBuilder was like this -

ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);
Expression exp = null;
Collection<Keywords> kwList = searchMedia.getKwCollection();
if (kwList != null && kwList.size() > 0) {
    for (Iterator it = kwList.iterator(); it.hasNext();) {
       KwList kw = (KwList) it.next();
       Expression expKw = eb.anyOf("kwCollection").equal(kw);
       if (exp == null) {
          exp = expKw;
       } else {
          exp = exp.and(expKw);
       }
    }
}

The result of a query constructed that way is for each iteration of the submitted keyword list a new join of the KEYWORDS and KW_MAP table is added.  But that will eventually hit the MySql 61 table join limit.

Now that I think about it, that makes sense due to the repeated adding of a new keyword expression during the iteration loop.

That leaves me with a question on how to perform the keyword "AND" search without exceeding the maximum MySql table joins?

-sonavor








Yes, sorry about the confusion on the expression.  I only showed the keyword part of my expression building code.  There is an overall Expression "exp" variable that I am constructing and the expKw is added to it.

Getting the keyword name from the keyword object and checking it against a keyword String list sounds like it could work.  I just think it would be slower than having the query comparing numeric primary keys.  Currently, in my search I have the keywords for the search entry selected from a multi-select list.  However, later I will probably be implementing a user-entered keyword search section where keywords are typed in so the String list compare would be better in that case.

Removing the prepareForExecution() and setFirstResult(0) didn't affect the SQL that gets generated.

If this is a bug in the way EclipseLink generates the SQL what is the procedure for logging a bug (I've never done that before).

-sonavor

James Sutherland wrote:

The SQL generate does not make sense, perhaps log a bug for this.

Your collection seems to be keyword objects, note the strings or ids.  You could convert the collection to the ids, or string and do,

Expression expKw = eb.anyOf("kwCollection").get("keyword").in(keywordStringList);

Also you had,
readAllQuery.setSelectionCriteria(exp);

I assume you meant expKw?

Also only set firstResult if you are using pagination, don't set it to 0.
readAllQuery.setFirstResult(0);

and don't call,
readAllQuery.prepareForExecution();

this is an internal method, and may be causing your issue.

sonavor wrote:

First of all, I had originally posted a question about using "IN" with a JPA query where I have two tables joined by a mapping table.  For some reason that posting no longer works this forum so I will re-state that basic problem.

Using a table for media called MEDIA_LISTING I have a second table called KEYWORDS. I didn't want to search for MEDIA_LISTING records using a text search for keywords that are associated so I created a mapping table called KW_MAP.

The table structures are as follows -

MEDIA_LISTING
MEDIA_ID   integer  PK
MEDIA_TITLE  varchar(50)
...other columns...

KEYWORDS
KW_ID  integer  PK
KW_NAME varchar(50)

KW_MAP
KW_ID       integer  PK
MEDIA_ID  integer   PK   ... PK comprised of both values

In my original posting I was having a problem determining the best way to have a query where I could find MEDIA_LISTING items by keywords.  I wanted to search for MEDIA_LISTING records where the MediaListing JPA entity's keywords Collection was "IN" a submitted Collection of keywords (named kwCollection in my case).

The solution I ended up with was to use EclipseLink's ExpressionBuilder and join the keyword table in the MediaListing query like this -

List<MediaListing> mList = new ArrayList<MediaListing>();
ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);

Expression expKw = eb.anyOf("kwCollection").in(kwList);

I then construct the query -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.setIsReadOnly(true);
readAllQuery.prepareForExecution();

...and execute it.

That all works fine.  However, after testing it for a while and increasing my test data size I ran into a problem where I found there is a limit to the number of keywords that can be used in the query.  

This is due to the resulting SQL that the ReadAllQuery generates from the Expression created by the ExpressionBuilder.

A small query for the MediaListing entity where I am searching for two keywords results in this SQL:

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, KW_MAP t3, KEYWORD t2, MEDIA_LISTING t1, KEYWORD t0
WHERE (((t0.KW_ID = ?) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID = t1.MEDIA_ID) AND (t0.KW_ID = t3.KW_ID))
AND ((t4.MEDIA_ID = t1.MEDIA_ID) AND (t2.KW_ID = t4.KW_ID)))) LIMIT ?, ?
bind => [405, 406, 0, 10]

Notice that for each keyword included in the query that two tables are joined - a KW_MAP join and a KEYWORD join.  There is a limit in MySql of 61 tables in a join.  When you factor in adding other MEDIA_LISTING columns to the query that eats up a lot of the query joins that available.

If I create a SQL statement myself to do the query I can do this -

SELECT DISTINCT t1.MEDIA_ID AS MEDIA_ID1, t1.MEDIA_TITLE AS MEDIA_TITLE6
FROM KW_MAP t4, MEDIA_LISTING t1 WHERE (t1.`MEDIA_ID` = t4.`MEDIA_ID`) AND
(t4.`KW_ID` IN (405, 406))

I understand that this query works because I am not including any t4 (KW_MAP) columns in the SELECT portion of the SQL.

My question is -
Is the solution for this query Join table limit to use a custom, native MySql query instead of the generated query that EclipseLink creates?

Thanks,
Sonavor