which produces ..

SELECT this_.*, anstns2.*
FROM   TS_CUSTOMERS this_
       left outer join TS_CUSTOMERS_ANS_TNS anstns2_
         on this_.ID = anstns2_.CUSTOMER_ID
WHERE  this_.BILLING_INSTANCE_ID is null
       and (this_.SAVED_ACCOUNT_ID is null)
       and exists (select 1
                   from   ts_customers_ans_tns a
                   where  this_.ID = a.CUSTOMER_ID
                          and a.TELEPHONE_NUMBER = <val>
                          and a.ACCOUNT_NUMBER = <val>)


------
Joe Brockhaus
[email protected]
------------


On Wed, Sep 21, 2011 at 6:23 PM, Joe Brockhaus <[email protected]> wrote:

> I would also settle for instruction on how to access the existing
> join alias (Customer has the List<AnTn> mapped inverse, join)
> thus producing ..
>
>    where Billing_ID is null and ..
>             and ( joinedantns.Telephone_Number = <val>
>                     and joinedantns.Account_Number = <val> )
>
>
> but for now, i've settled with direct sql ..
>
>     var tempCustQuery =
> session.CreateCriteria<TechSupportCustomer>("tempCusts")
>
>         .Add(nhc.Expression.IsNull("BillingInstanceID"))
>         .Add(nhc.Expression.Sql("({alias}.SAVED_ACCOUNT_ID is null)"))
>         .Add(nhc.Expression.Sql(new StringBuilder().Append(
>             "exists (select 1 from ts_customers_ans_tns a").Append(
>             "    where {alias}.ID = a.CUSTOMER_ID ").Append(string.Format(
>             "      and a.TELEPHONE_NUMBER = '{0}'",
> dslPhone)).Append(string.Format(
>             "      and a.ACCOUNT_NUMBER = '{0}')",
> billingPhone)).ToString()))
>
>         .SetResultTransformer(new
> NHibernate.Transform.DistinctRootEntityResultTransformer());
>
>     var results = tempCustQuery.List<TechSupportCustomer>();
>
>
> ------
> Joe Brockhaus
> [email protected]
> ------------
>
>
>   On Wed, Sep 21, 2011 at 5:35 PM, Joe Brockhaus <[email protected]>wrote:
>
>> If I remove:
>>
>>         .Add(Property.ForName("ID").EqProperty("antns.ID"));
>>
>> I don't get an exception, but the resulting query is wrong:
>>
>>         ... and exists ( select 1 from customers_antns antns
>>                               where antns.TN = <val> and antns.AN = <val>
>> )
>>
>> (it doesn't restrict the sub-query correctly .. )
>>
>> how can I make this work?
>>
>> I've also tried creating an alias for the ID of the root customer query,
>> but that errors w/:
>>
>>         "ID is not an association"
>>
>>      // .. sub-query
>>      .Add(Restrictions.EqProperty("CustomerID", "custID"));
>>        // add the subquery to the customer query
>>      tempCustQuery
>>         .CreateAlias("ID", "custID")
>>
>>         .CreateCriteria("ANsTNs", "antns")
>>             .Add(Subqueries.Exists(antnMatchQuery));
>>
>>
>> ------
>> Joe Brockhaus
>> [email protected]
>> ------------
>>
>>
>>   On Wed, Sep 21, 2011 at 4:45 PM, Joe Brockhaus <[email protected]>wrote:
>>
>>> i'm getting a key not found error on
>>> tempCustQuery.GetExecutableCriteria(session)
>>>
>>> i'm using this as a guide:
>>> http://ayende.com/blog/1900/complex-searching-querying-with-nhibernate
>>>
>>> I'm trying to get all customers that have a child with the given 2 column
>>> values.
>>>
>>>     // temp customer query
>>>     var tempCustQuery =
>>> DetachedCriteria.For<TechSupportCustomer>("tempCusts")
>>>         .Add(nhc.Expression.IsNull("BillingInstanceID"))
>>>         .Add(nhc.Expression.Sql("({alias}.SAVED_ACCOUNT_ID is null)"))
>>>         .SetResultTransformer(new
>>> NHibernate.Transform.DistinctRootEntityResultTransformer());
>>>
>>>     // create the subquery for the antn matches
>>>     var antnMatchQuery = DetachedCriteria.For<TechSupportCustomerAnTn>()
>>>         .Add(Restrictions.Eq("TelephoneNumber", dslPhone))
>>>         .Add(Restrictions.Eq("AccountNumber", billingPhone))
>>>         .SetProjection(Projections.Id()) // only query/hydrate the ID in
>>> the final query
>>>         .Add(Property.ForName("ID").EqProperty("antns.ID"));
>>>
>>>     // add the subquery to the customer query
>>>     tempCustQuery.CreateCriteria("ANsTNs", "antns")
>>>         .Add(Subqueries.Exists(antnMatchQuery));
>>>
>>>    // get resulting customer entities
>>>     var results = tempCustQuery.GetExecutableCriteria(session)
>>>         .List<TechSupportCustomer>();
>>>
>>> in SQL:
>>>
>>> select * from customers c
>>> where BILLING_ID is null and SAVED_ID is null
>>> and exists ( select 1 from customers_antns antns
>>>                   where antns.cust_id = c.id
>>>                   and antns.TN = <value> and antns.AN = <value>)
>>>
>>> what am I doing wrong?
>>> (and at this point, why shouldn't I just do it with Expression.Sql??)
>>>
>>> ------
>>> Joe Brockhaus
>>> [email protected]
>>> ------------
>>>
>>
>>
>

-- 
You received this message because you are subscribed to the Google Groups 
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to 
[email protected].
For more options, visit this group at 
http://groups.google.com/group/nhusers?hl=en.

Reply via email to