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