A Where clause on a prefetchpath

Posts   
 
    
LarsC
User
Posts: 8
Joined: 20-Aug-2019
# Posted on: 20-Aug-2019 09:54:16   

(LLBLGen Pro V5.5, adapter. I guess this problem is not related to database type etc. I can provide those details if needed.)

With a standard schema where a Customer can have multiple Orders.

How do I retrieve all Customers of a region which has at least one Order with Status "Pending".

This was the existing code which retrieves all Customers including all Orders for a certain region with paging.

var qf = new QueryFactory();

var q = qf.Customer
    .Where(CustomerFields.Region.Equal("Europe"))
    .WithPath(CustomerEntity.PrefetchPathOrders
        .WithOrdering(OrderFields.Nr.Ascending()))
    .OrderBy(CustomerFields.ID.Descending())
    .Page(pageNr, customersPerPage);

Tried adding this filter on PrefetchPathOrders

.WithFilter(OrderFields.Status.Equal("Pending"))

But this will only filter all orders and the result will include Customers which had no pending orders.

Do I have to use joins of some sort?

I'd like to think that I, instead of a filter, could add

.Where(OrderFields.Status.Equal("Pending"))

Or something similar.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Aug-2019 19:53:24   

Yes you should use Joins to filter on the main entity you are fetching. PrefetchPaths are there to fetch related entities to filter the main entity list.

Example:

var query = qf.Order
     .From(QueryTarget.InnerJoin(qf.OrderDetail)
    .On(OrderFields.OrderId == OrderDetailFields.OrderId))
    .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId)).Where(ProductFields.CategoryId == 2)
LarsC
User
Posts: 8
Joined: 20-Aug-2019
# Posted on: 26-Aug-2019 12:43:10   

Walaa wrote:

Yes you should use Joins to filter on the main entity you are fetching. PrefetchPaths are there to fetch related entities to filter the main entity list.

Example:

var query = qf.Order
     .From(QueryTarget.InnerJoin(qf.OrderDetail)
    .On(OrderFields.OrderId == OrderDetailFields.OrderId))
    .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId)).Where(ProductFields.CategoryId == 2)

Thanks. It helped!