Select in select clause (or subselect, subquery...)

ServiceStack.OrmLite
1

Hello, please can be written with typped OrmLite and strong entities this query?

Select tab.*, (Select Count(*) From MyTable Where ParentId=tab.Id) As ChildCount
From MyTable tab 
Where tab.ParentId Is Null

…I know I can use Group By, or pure sql, but I would like use strongly typped entities (with naming conversion etc.).

Thank you, TH.

2

I will add my current solution. I would like to avod the hand-writed custom sql query at ChildCount computing.

var exp = db.From<Entities.Object>();

if (query.ParentId.HasValue)
    exp.And(x => x.ParentId == query.ParentId);
else
    exp.And(x => !x.ParentId.HasValue);

exp = exp.Limit(query.Paging.Skip, query.Paging.PageSize);

exp = exp.Select(x =>
    new
    {
        x,
        ChildCount = Sql.Custom("(Select Count(*) From \"object\" x Where x.parent_id=\"object\".id)"),
        TotalRows = Sql.Custom("COUNT(*) OVER()")
    });

var ds = db.Select<ObjectListItem>(exp);
3

The only sub select typed expression support is within a WHERE IN statement.

You can get typed column and table names in your custom SQL using the Column<T> and Table<T> extension methods.

ChildCount = Sql.Custom($$"(Select Count(*) From {exp.Table<Entities.Object>()} x Where x.parent_id=\"object\".id)"),

You should also be able to render the Sub Select as to a SQL Statement and include that, something like:

var q = db.From<Entities.Object>(db.TableAlias("x"));
q.Where(x => x.ParentId == Sql.Custom(q.Column<Entities.Object>(c => c.Id, true)));
var subSql = q.Select(Sql.Count("*")).ToSelectStatement();

exp = exp.Select(x =>
    new
    {
        x,
        ChildCount = Sql.Custom($$"({subSql})"),
        TotalRows = Sql.Custom("COUNT(*) OVER()")
    });
4

The second solution is exactly what I’m looking for. I modified it this way (it might be useful for someone else):

var exp = db.From<Entities.Object>();
exp.And(x => !x.ParentId.HasValue);
    
var q = db.From<Entities.Object>(db.TableAlias("x"));
q.Where($$"x.{q.Column<Entities.Object>(qp => qp.ParentId)}={exp.Column<Entities.Object>(ep => ep.Id, true)}");
var subSql = q.Select(Sql.Count("*")).ToSelectStatement();

exp = exp.Select(x =>
	new
	{
		x,
		ChildCount = Sql.Custom($$"({subSql})"),
		TotalRows = Sql.Custom("COUNT(*) OVER()")
	});

var ds = db.Select<ObjectListItem>(exp);

…query above generate this sql:

SELECT "object"."id", "object"."parent_id", "object"."name",
     (SELECT COUNT(*) FROM "object" "x" WHERE x."parent_id" = "object"."id") AS ChildCount,
     COUNT(*) OVER () AS TotalRows
FROM "object"
WHERE NOT(("parent_id" IS NOT NULL))

Ok, I also mention it a little intentionally :-))
It’s possible to notice, that “parent_id” is not prefixed with “object” like columns in Select clause - with more same tables, this might be a problem, but maybe it’s treated, I admit that I don’t know.

But, when I add “TableAlias” for “main” table in this way:

var exp = db.From<Entities.Object>(db.TableAlias("obj"));
//...rest of code is same as above

I get this result with error:

SELECT "object"."id", "object"."parent_id", "object"."name",
     (SELECT COUNT(*) FROM "object" "x" WHERE x."parent_id" = "object"."id") AS ChildCount,
     COUNT(*) OVER () AS TotalRows
FROM "object" "obj"
WHERE NOT(("obj"."parent_id" IS NOT NULL))

Is there some magic how to make them in the Select clause “obj” instead of “object” (and same for subquery). And also tell in the exp.Column<>(…) which table alias to use, especially with more joined (and aliased) tables?

Thank you!
Tom

1 Like
5

The 2 queries are completely separate, they don’t have any impact on the each other query.

There are examples of different ways TableAliases’s can be used in this example:

e.g. you could Sql.TableAlias() like:

q.Where(x => x.ParentId == Sql.TableAlias(x.Id, "obj"));
6

Thank you for your response. I will try the part with column alias. But in the second part I was probably wrong and I was not understood:-)

This behavior seemed bad to me (simplified version):

var sqlExpression = db.From<Entities.Object>(db.TableAlias("obj"));
sqlExpression = sqlExpression.Select(x => new { x, TotalRows = Sql.Custom("COUNT(*) OVER()") });
var result = db.Select(sqlExpression);

generates this bad sql query:

SELECT "object"."id", "object"."parent_id", "object"."name", COUNT(*) OVER () AS TotalRows
FROM "object" "obj"

Is problem with my coding (or using table alias with total rows counting) or with generation sql query?

7

And one another question (I don’t want to dirty the main discussion thread) - do you plan to add “Global DeleteFilter” feature to OrmLiteConfig?

8

The latest v5.5.1 on MyGet should now use the Table Alias if defined.

No, most delete APIs use an Id or a condition, there’s no access to the entity like there is with Update/Delete APIs.