thanks for all your help, i didn't realize the limit was being applied
to the query because it wasn't actually in the query displayed in the
logs. (I had always thought it should be)
I'm not too sure how I am going to resolve this issue, but will likely
just end up using a native query
Derek Knapp
Software Engineer
itracMEDIA, Inc.
1560
Queen Street East, Toronto, ON M4L 1E9 | Tel: 416.364.9444 |
Fax:416.364.9589
Tom Ware wrote:
Hi
Derek,
The one word answer is.... Yes, it is intentional.
MaxResults is being applied to the SQL executed by EclipseLink.
Doing it that way is really the only way maxResults can be designed to
optimize your query. To return 10 entities from the query below, we
would have to execute some other queries to determine how many DB rows
to select and essentially remove any optimization setMaxResults
provides.
-Tom
Derek Knapp wrote:
is how it deals with the
*query.setMaxResults(10); *also intentional?
for example, if the top 10 results are the same contact, it will only
return 1 result, not the same contact 10 times... if it is smart enough
to do this, shouldn't it return the top 10 unique contacts instead of
the top x unique contacts in the top 10 results...
for example,
1, bob barkey, bob, barkey, mr, director, fake@xxxxxxxxx, 1
1, bob barkey, bob, barkey, mr, director, fake@xxxxxxxxx, 2
1, bob barkey, bob, barkey, mr, director, fake@xxxxxxxxx, 3
2, bob1 barkey1, bob1, barkey1, mr, director, fake@xxxxxxxxx, 1
2, bob1 barkey1, bob1, barkey1, mr, director, fake@xxxxxxxxx, 2
2, bob1 barkey1, bob1, barkey1, mr, director, fake@xxxxxxxxx, 3
2, bob1 barkey1, bob1, barkey1, mr, director, fake@xxxxxxxxx, 4
3, bob2 barkey2, bob2, barkey2, mr, director, fake@xxxxxxxxx, 1
3, bob2 barkey2, bob2, barkey2, mr, director, fake@xxxxxxxxx, 2
3, bob2 barkey2, bob2, barkey2, mr, director, fake@xxxxxxxxx, 3
will return
1, bob barkey, bob, barkey, mr, director, fake@xxxxxxxxx
2, bob1 barkey1, bob1, barkey1, mr, director, fake@xxxxxxxxx
3, bob2 barkey2, bob2, barkey2, mr, director, fake@xxxxxxxxx
Derek Knapp
/Software Engineer/
*itrac**MEDIA, Inc.*
1560 Queen Street East, Toronto, ON M4L 1E9 | Tel: 416.364.9444
| Fax:416.364.9589
itrac_logo
Tom Ware wrote:
Hi Derek,
The behavior you are seeing is actually some behavior that
EclipseLink takes beyond the JPA specification.
The spec defines what can be used in an Order by in section 4.9. For
a query that selects individual fields, only those fields that are
selected can be used in the Order by clause. (A query that selects a
whole Entity can use any of the fields from that Entity)
I believe the reason for this restriction is that some databases do
not allow SQL that orders by a non-selected field (but I am not
certain).
EclipseLink is allowing you to Order by something that is not
selected, but in order to do that, it is transparently adding the item
that is in the selected list.
-Tom
Derek Knapp wrote:
any ideas on this one??
is this the intended behavior of the ORDER BY? or should I be
submitting a bug report?
Derek Knapp
Derek Knapp wrote:
public List<Contacts> recentTouches(Subuser subuser)
{
Query query = em.createQuery("SELECT DISTINCT h.contact FROM
History h WHERE h.subuser = :subuser ORDER BY h.id DESC");
query.setParameter("subuser", subuser);
query.setMaxResults(10);
return query.getResultList();
}
*Generates the following sql*
SELECT DISTINCT t0.contactid, t0.fullname, t0.firstname, t0.lastname,
t0.salutation, t0.title, t0.email, t0.mailingaddressline1,
t0.mailingaddressline2, t0.mailingaddressline3, t0.city, t0.province,
t0.postalcode, t0.country, t0.company, t0.businessphone,
t0.businessfax, t0.mobilephone, t0.homephone, t0.businessphoneext,
t0.smsphone, t0.leadsource, t0.birthdate, t0.emailoptout,
t0.create_date, t0.last_modified, *t1.id* FROM history t1 LEFT OUTER
JOIN contacts t0 ON (t0.contactid = t1.contactid) WHERE (t1.subuserid =
?) ORDER BY t1.id DESC
*For whatever reason, it has added in t1.id in to the list of items
selected…. So the number of results from the above function varies
depending if there are any duplicate contacts in the top 10 results
returned from the query… why is it adding t1.id to the select list? I
don’t care about its value….*
* *
* *
*Derek Knapp*
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4378 (20090828) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
|