Re: [eclipselink-users] EclipseLink Many-to-Many query problems with MyS

[sonavor <jw@xxxxxxxxxxxxxx>]

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
>> 
>> 
>> 
>> 
> 
> 

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