Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] EclipseLink Many-to-Many query problems with MySql 
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
> 
> 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/EclipseLink-Many-to-Many-query-problems-with-MySql-tp25140830p25151382.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

Back to the top