I went with HQL and saved the hassles. Any tips on improving this
would be appreciated though. One thing I would do in native sql is
change the date comarison stuff to come from a single query as a
table, i.e.
FROM
(
SELECT MIN, MAX
FROM ..
) T
public IList<Order> GetOrdersBy(string referenceNumber = null, int?
customerId = null, int? carrierId = null, DateTime? startDate = null,
DateTime? endDate = null, int? websiteId = null,
OrderStatus? status = null)
{
var byStatusHql =
@"
select odor
from Order odor
join fetch odor._orderStatusLog statusLog
where
(:referenceNumber is null or
odor.Quote.ReferenceNumber=:referenceNumber) and
(:customerId is null or
odor.Quote.Customer.CustomerID=:customerId) and
(:carrierId is null or
odor.Quote.Carrier.CarrierID=:carrierId) and
(:websiteId is null or
odor.Quote.Customer.Website.WebsiteID=:websiteId) and
(
:startDate is null or :startDate >
(select min(CreatedDate) from OrderStatusLog lg where
lg in elements(odor._orderStatusLog))
) and
(
:endDate is null or :endDate <=
(select max(CreatedDate) from OrderStatusLog lg where
lg in elements(odor._orderStatusLog))
) and
(
:status is null or :status =
(
select Status from OrderStatusLog lg where lg in
elements(odor._orderStatusLog)
and lg.OrderStatusLogID = (select
max(OrderStatusLogID) from OrderStatusLog lgg where lgg in
elements(odor._orderStatusLog))
)
)
";
var query = Session.CreateQuery(byStatusHql);
query.SetParameter("referenceNumber", referenceNumber)
.SetParameter("customerId", customerId)
.SetParameter("carrierId", carrierId)
.SetParameter("websiteId", websiteId)
.SetParameter("startDate", startDate)
.SetParameter("endDate", endDate)
.SetParameter("status", status)
;
return query.List<Order>();
}
On Jun 4, 11:09 pm, Corey Coogan <[email protected]> wrote:
> I need to build a dynamic query from a search form. I have been using
> Linq for NH and it's been great when the queries are simple. I just
> build the query for the passed parameters, like so:
>
> public IList<Order> GetOrdersBy(int? customerId=null, int? carId=null,
> string status=null)
> {
> var query = Session.Query<Order>();
> if(customerId.HasValue)
> query = query.Where(x => x.status == status);
>
> ...
> ...
>
> }
>
> Pretty simple since everything is on the same table.
>
> Now I need to track the status everytime it changes so there is a new
> table that looks like this:
>
> OrderStatusLog
> -----------------------
> id int
> orderId int
> status varchar
> createdate datetime
>
> When someone searches for a status, I need to search against the MAX
> status. When someone searches on a date range, I want to search
> between min(createdate) and max(createdate). I tried to do this with
> a GROUP BY in linq but I got a notimplemented. What is the best way
> to do this? I was avoiding QueryOver and Criteria for much of this
> because it always trips me up.
>
> Any suggestions before I hack together an HQL string?
--
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.
