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
itracMEDIA, Inc.
1560
Queen Street East, Toronto, ON M4L 1E9 | Tel: 416.364.9444 |
Fax:416.364.9589
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
|
