Correct way to do JOIN with LIMIT on first table

ServiceStack.OrmLite MySql
1

Using MySQL (5.7+) what would be the right way to set up the OrmLite query to apply a limit to the first table in a join?

For example, say I have Customer and Order, and might do an SQL query like

SELECT * FROM
(SELECT * FROM Customer
WHERE Country=‘US’
ORDER BY Name
LIMIT 0,10
) AS Customer
INNER JOIN Order ON (Order.CustomerId = Customer.Id)

which gives me the first 10 customer and all their orders.

But currently, when I set up my query and call SelectMultiAsync I get a List<Tuple<Customer,Order>> with the first 10 orders and their customers.

2

Hi @cdmackie,

Could you share the query you tried with the Customer/Order class setup with associated attributes?

You can capture what SQL is being generated from OrmLite queries using Db.GetLastSql(); which can be useful working out why more complicated queries might be resulting in different than expected behavior.

Saying that, sometimes it is easier to use straight SQL rather than building the query using expressions, or alternatively using views and dedicated view classes.

3

The only support for nested sub selects is OrmLite’s Nested Typed Sub SqlExpressions support, e.g:

var usCustomerIds = db.From<Customer>(c => c.Country == "US")
    .OrderBy(x => x.Name)
    .Take(10)
    .Select(c => c.Id);
var usCustomerOrders = db.Select(db.From<Order>()
    .Where(x => Sql.In(x.CustomerId, usCustomerIds)));

Otherwise you’d need to use the Custom SQL APIs to execute the exact query you want.

4

Thanks for the suggestions. Is there any plans to add subselect features?

Anyway, in case it helps anyone else, for now I could use the built query as you showed and then a bit of tweaking to the SQL.

e.g.

    var innerquery = Db.From<Customer>().Where(c => c.Country == country).OrderBy(c => 
        c.Name).Skip(skip).Take(limit);
    var query =  Db.From<Customer>($"({innerquery.ToSelectStatement()}) AS 
        `{Db.GetTableName<Customer>()}`");
    query = query.Join<Order>((c,o) => c.Id == o.CustomerId);
    query.Params = innerquery.Params;

    var list = Db.SelectMulti<Customer, Order>(query);

However, I do also have to disable the keyword checking around building the query else it picks it up as unsafe.

    var oldfn = OrmLiteUtils.SqlVerifyFragmentFn;
    OrmLiteUtils.SqlVerifyFragmentFn = new Func<string, string>(s => s);
    :
    OrmLiteUtils.SqlVerifyFragmentFn = oldfn;
5

There’s typically not a good discoverable typed API that could be generically usable for a wide range of sub selects that would be more intuitive than the equivalent custom SQL with typed symbol names.