Databases
StackOverflow2013
Postsdbo
POHQL join problem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHQL join problem
    primarykey
    data
    text
    <p>I have no problem writing sql for the following problem. However, I would like to write it as HQL or ICriteria. I am using fluent nhibernate and the latest nhibernate. The situation:</p> <p>There are 6 classes A B C D AC AD. B inherits from A. AC represents a m:m relationship between A and C and AD represents a m:m relationship between A and D. Let us assume that all classes have an ID column. I would like to count the number of Cs and Ds B is associated with.</p> <p>There is no IList of, for example, Cs in A (and B for that matter). Still the classes are associated ...</p> <p>Here is some code (simplified):</p> <pre><code>public class A : Entity { } public class B : A { } public class C : Entity { } public class D : Entity { } public class AC : Entity { public virtual A A { get; set; } public virtual C C { get; set; } } public class AD : Entity { public virtual A A { get; set; } public virtual D D { get; set; } } </code></pre> <p>Is this possible to use HQL and ‘left join’ (to also show the Bs that have zero Cs and Ds) in my particular case?</p> <p>Thanks.</p> <p>Christian</p> <p>PS: </p> <p>I have played a bit with theta style joins but did not get the expected results and I don’t think ‘left joins’ are possible here aren’t they?</p> <p>PPS:</p> <p>This theta-style join kind of works but only if B is assciated with at least 1 C and D:</p> <pre><code>select B.Id, count(distinct AC.C.Id), count(distinct AD.D.Id) from AC AC, AD AD, B B where AC.A.Id = B.Id and AD.A.Id = B.Id group by B.Id </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScs0815
    1. UScs0815
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COcan you please provide the classes as code? It would be much easier to understand which properties are available.
      singulars
      1. POHQL join problem
      1. USStefan Steinegger
    2. COdone. see class code. is this ok? please note that things are simplified.
      singulars
      1. POHQL join problem
      1. UScs0815
    3. COWhy have you got classes to represent the M:M relationship? You're not associating any extra data, so it seems frivolousness and confusing
      singulars
      1. POHQL join problem
      1. USDavid Kemp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload