Self Join selecting row with max column value over a group?

Posts   
 
    
scotru
User
Posts: 104
Joined: 16-Feb-2006
# Posted on: 18-Nov-2019 02:21:28   

How to implement the following SQL using low level API on self-servicing objects:

SELECT xlc1.*
      FROM xpt_lsu_charges xlc1 LEFT JOIN xpt_lsu_charges xlc2
      ON(xlc1.LSU_ID = xlc2.LSU_ID) AND(xlc1.term_id = xlc2.term_id) AND(xlc1.SET_SEQ < xlc2.SET_SEQ)
      WHERE(xlc2.SET_SEQ IS NULL) AND(xlc1.term_id = 32);

(returns the greatest SET_SEQ row in a group on LsuId and TermId)

See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 18-Nov-2019 04:51:01   

You can add a manual (not generated) relationship when you fetch an EntityCollection. Then you can add a CustomFilter to the relation to include any predicate. (Ref...). Example using Northwind Low Level API:

SQL to mimic

SELECT Orders.*
    FROM Orders
    LEFT JOIN Orders O2 
        ON Orders.CustomerID = O2.CustomerID 
        AND Orders.EmployeeID = O2.EmployeeID
        AND Orders.OrderID < O2.OrderID
WHERE O2.OrderID IS NULL AND Orders.CustomerID = 'ALFKI'

Fetch code using SelfServicing in v5.6

// create a custom relationship
var specialRelation = new EntityRelation(OrderFields.CustomerId, OrderFields.CustomerId, RelationType.OneToMany);
specialRelation.AddEntityFieldPair(OrderFields.EmployeeId, OrderFields.EmployeeId);
// add a special filter to the relationship
specialRelation.CustomFilter = new PredicateExpression(OrderFields.OrderId < OrderFields.OrderId.SetObjectAlias("O2"));

var relations = new RelationCollection();
relations.Add(specialRelation, "O2", JoinHint.Left); // set a alias for the second (end) entity

var filter = new PredicateExpression();
filter.Add(OrderFields.OrderId.SetObjectAlias("O2") == DBNull.Value);
filter.Add(OrderFields.CustomerId == "ALFKI");

var orders = new OrderCollection();
orders.GetMulti(filter, relations);

Hope that helps simple_smile

David Elizondo | LLBLGen Support Team