1

How can I express the query below (from this question):

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
WHERE p2.id IS NULL;

Using OrmLite Select and Join API?

Community
  • 1
  • 1
kolrie
  • 12,562
  • 14
  • 64
  • 98

1 Answers1

1

Unfortunately your mutli tables self table join is too complex to be expressed in OrmLite's Typed API so you'll need to drop down to Custom SQL, e.g:

var results = db.Select<Tuple<Customer,Purchase>>(@"SELECT c.*, 0 EOT, p1.*
    FROM customer c
    JOIN purchase p1 ON (c.id = p1.customer_id)
    LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
        (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
    WHERE p2.id IS NULL;");

results.PrintDump();

I've created a Live Example of this you can play with on Gistlyn.

mythz
  • 141,670
  • 29
  • 246
  • 390
  • Thank you so much. Is it possible to combine this query by adding other Where clauses using the API? If not, what is the correct way to you pass in optional WHEREs and Limit (offset, rows) parameters? – kolrie Apr 06 '17 at 02:32
  • @kolrie can you ask a new question with an example of what you're trying to do? – mythz Apr 06 '17 at 02:44
  • http://stackoverflow.com/questions/43244785/parameterizing-a-servicestack-custom-sql-query @mythz – kolrie Apr 06 '17 at 02:46