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.