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

[sonavor <jw@xxxxxxxxxxxxxx>]

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








sonavor wrote:
> 
> 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-tp25140830p25166534.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.