Databases
StackOverflow2013
Postsdbo
POSubquery parent id in NHibernate QueryOver
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSubquery parent id in NHibernate QueryOver
    text
    copied!<p>I'm trying to understand how this may work. What I would have is to have all Trees from a given list of IDs that has not rotten apples. Looks easy but I'm fresh of NHibernate, not so good in SQL and as you can see I'm stuck.</p> <p>I wrote this code down here:</p> <pre><code>Tree treeitem = null; QueryOver&lt;Apple&gt; qapple = QueryOver.Of&lt;Apple&gt;() .Where(x =&gt; (!x.IsRotten)) .And(Restrictions.IdEq(Projections.Property&lt;Tree&gt;(y =&gt; y.Id))) // Or this one... //.And(Restrictions.EqProperty( // Projections.Property&lt;Apple&gt;(y =&gt; y.Tree.Id), // Projections.Property&lt;Tree&gt;(y =&gt; y.Id))) .Select(x =&gt; x.Id); return this.NHibernateSession.QueryOver&lt;Tree&gt;() .Where(x =&gt; x.Id.IsIn(ListOfTreeId)) .WithSubquery.WhereExists&lt;Apple&gt;(qapple) .SelectList(list =&gt; list .Select(z =&gt; z.Id).WithAlias(() =&gt; treeitem.Id) .Select(z =&gt; z.Name).WithAlias(() =&gt; treeitem.Name) .Select(z =&gt; z.Type).WithAlias(() =&gt; critem.Type) .TransformUsing(Transformers.AliasToBean&lt;Tree&gt;()) .List&lt;T&gt;(); </code></pre> <p>And the pseudo SQL I get is something like this:</p> <pre><code>SELECT id, name, type FROM trees WHERE id IN (1, 2, 3) AND EXIST(SELECT id FROM apples WHERE NOT rotten AND apples.idtree = apples.id) </code></pre> <p>As you can see there's a problem with the subquery that use the same table Id instead of something like that:</p> <pre><code>EXIST(SELECT id FROM apples WHERE NOT rotten AND apples.idtree = tree.id) </code></pre> <p>I'm bit lost actually. Maybe there's another way to build this up. Any help is welcome, thanks.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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