3124

openJPA outer join on optional many-to-one when have order by clause

Question:

I have a scenario, that I would like the generated SQL always using Outer Join for optional ManyToOne. I am using OpenJPA 2.2.1.x.

Two entities: Event and Rule, Event have an optional ManyToOne unidirectional relationship to Rule. Following are the two entity classes:

@Entity public class Event { @Id private String uuid; private Date eventTime; @ManyToOne(optional=true) private Rule rule; } @Entity public class Rule { @Id private String uuid; private int rowNum; }

If in JPQL I use ORDER BY on Event attribute(for ex, eventTime), it generates LEFT OUTER JOIN between event and rule table. JPQL:

SELECT e from Event e order by e.eventTime desc

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t0.eventTime DESC

If in JPQL I use ORDER BY on Rule attribute(for ex, rowNum), it generates INNER JOIN between event and rule table. JPQL:

SELECT e from Event e order by order by e.rule.rowNum asc

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 INNER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t1.rowNum ASC

My questions are:

<ol><li>Is this the correct behaviour that based on different ORDER BY clause, two different types of JOIN are generated? </li> <li>Is there any way we can make openJPA always generate OUTER JOIN, other than use native SQL? (I tried LEFT OUTER JOIN FETCH, it doesn't help :-(). </li> </ol>

Thanks in advance,

David Liu

Answer1:

Using LEFT JOIN could achieve the goal.

SELECT e FROM Event e LEFT JOIN e.rule r ORDER BY r.rowNum ASC

which will generate the following SQL:

SELECT t0.uuid, t0.eventTime, t2.uuid, t2.rowNum, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid LEFT OUTER JOIN Rule t2 ON t0.RULE_UUID = t2.uuid ORDER BY t1.rowNum ASC

There is something in the JPQL need to pay attention: the ORDER BY must be applied to r.rowNum rather than e.rule.rowNum, otherwise, it will generate an INNER JOIN.

You may have noticed that the generated SQL have two OUTER JOINs on RULE table.

Recommend

  • Formatting ISO 8601 in Java 7
  • Issue with Long click using instrumentation class in uiautomator for 4.3 android version
  • Flink Kafka - how to make App run in Parallel?
  • How to write an RSpec test for Ryan Bates' helper from screencast #228?
  • How to deal with xxx~HEAD after git merge?
  • Event files in Google Tensorflow
  • Winform Designer Application
  • several hexbin plots on one PDF page in R
  • TelephonyManager crashing on android studio
  • How do I get an Option instead of an Option from a Diesel query which only returns 1 or 0 records?
  • MOC adding namespace to class names
  • ASP Error 0223 - TypeLib Not Found, intermittent, resolved after IIS restart
  • Socket io in node app on google app engine
  • C++ Boost ptr_map serialization error
  • Usage of local dart library
  • HighCharts - Show tooltip on column where value is 0 or null
  • triggering user space with kernel
  • Illegal mix of collations for operation for date/time comparison
  • Javascript + PHP Encryption with pidCrypt
  • Release, debug version and Authorization Google?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Hits per day in Google Big Query
  • JTable with a ScrollPane misbehaving
  • Getting Messege Twice Using IMvxMessenger
  • How to stop GridView from loading again when I press back button?
  • apache spark aggregate function using min value
  • unknown Exception android
  • sending mail using smtp is too slow
  • Busy indicator not showing up in wpf window [duplicate]
  • Sorting a 2D array using the second column C++
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Observable and ngFor in Angular 2
  • How can i traverse a binary tree from right to left in java?
  • How can I use `wmic` in a Windows PE script?
  • failed to connect to specific WiFi in android programmatically
  • Unable to use reactive element in my shiny app
  • How can I use threading to 'tick' a timer to be accessed by other threads?