[eclipselink-users] EclipseLink Many-to-Many query problems with MySql

[sonavor <jw@xxxxxxxxxxxxxx>]

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



-- 
View this message in context: http://www.nabble.com/EclipseLink-Many-to-Many-query-problems-with-MySql-tp25140830p25140830.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.