Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Self Join selecting row with max column value over a group?
 

Pages: 1
LLBLGen Pro Runtime Framework
Self Join selecting row with max column value over a group?
Page:1/1 

  Print all messages in this thread  
Poster Message
scotru
User



Location:
College Place, WA
Joined on:
16-Feb-2006 01:03:42
Posted:
104 posts
# Posted on: 18-Nov-2019 02:21:28.  
How to implement the following SQL using low level API on self-servicing objects:

Code:
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



  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8098 posts
# 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
Code:
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
Code:
// 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 Regular Smiley



David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.