Hash selection

Question

Is there a way that I can have a hash with a subquery inside the SELECT clause that will be used in the SqlUtil :: AbstractTable methods (like getRowIterator ()).

I found something similar in select_option_superquery , however the subquery used there was found in the FROM clause:

SELECT serviceid, service_methodid FROM (SELECT serviceid,service_methodid..)...

while I'm looking for something like:

    SELECT  t1.id, t1.order_id, 
     (SELECT COUNT(order_id) FROM tbl1 t2 WHERE t1.order_id = t2.order_id) as count, 
     t1.other_cols,
     t3.other_cols 
    FROM tbl1 t1 left join tbl3 s on t1.id = t3.id

Desired Result - Count in Result Set order_id

tbl1
id   order_id other_cols
1    ord1     ...
2    ord2     ...
3    ord1     ...

Result:

id   order_id   count   other_cols
1    ord1       2       ...
2    ord2       1       ...
3    ord1       2       ...

Answer 1

, ( ), , superquery, , SQL SqlUtil cop_over()

:

list cols = (
    "id",
    "order_id",
    cop_as(cop_over(cop_count("order_id"), "order_id"), "count"),
    # ... other columns to select here - this is in the inner query
    );

hash sh = (
    "columns": cols,
    "join": join_inner(t2, "t2", ("order_id": "order_id")) +
            join_inner(t3, "t3", ("id": "id")),
    "superquery": (
        "columns": (
            cop_distinct("id"), "order_id", "count"
            # note put columns needed here without table prefixes, this is for the outer query
        ),
    ),
    );

SqlUtil, - , ( ) - , . , , t2.

t2 , join_left() join_inner().

SQL :

string sql;
list l = t1.selectRows(sh, \sql);
log(LL_INFO, "sql: %s", sql);
log(LL_INFO, "SQL results: %N", l);

, !