simolus3
Inner join with select
I am having a hard time achieving this scenario
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_inner
SQL For selecting the lowest OrderDetailId for an OrderId
SELECT Orders.OrderID, OrderDetails.OrderDetailID
FROM Orders
INNER JOIN (
SELECT *
FROM OrderDetails
ORDER BY OrderDetailID ASC
) OrderDetails ON Orders.OrderId = OrderDetails.OrderId
WHERE Orders.OrderID == '10248'
group by Orders.OrderID;
SQL For selecting the lowest OrderDetailId for an OrderId
SELECT Orders.OrderID, OrderDetails.OrderDetailID
FROM Orders
INNER JOIN (
SELECT *
FROM OrderDetails
ORDER BY OrderDetailID DESC
) OrderDetails ON Orders.OrderId = OrderDetails.OrderId
WHERE Orders.OrderID == '10248'
group by Orders.OrderID;
But I am failing to get the same result with MOOR
return (select(dbOrderTable).join([
innerJoin(
dbOrderDetailTable,
dbOrderDetailTable.orderId.equalsExp(dbOrderTable.orderId),
)
])
..orderBy([
OrderingTerm.desc(dbOrderTable.orderDetailId),
])
..groupBy([dbOrderTable.orderId]))
.get();
After 2 hours I figured out how to do it with a custom query but a custom query is not the preferred way
final orderTableName = dbOrderTable.$tableName;
final orderDetailTableName = dbOrderDetailTable.$tableName;
final sqlQuery = '''
SELECT *
FROM $orderTableName
INNER JOIN (
SELECT *
FROM $orderDetailTableName
ORDER BY ${dbOrderDetailTable.orderDetailId.$name} DESC
) $orderDetailTableName ON $orderTableName.${dbOrderTable.orderId.$name} = $orderDetailTableName.${dbOrderDetailTable.orderId.$name}
GROUP BY $orderTableName.${dbOrderTable.orderId.$name};
''';
return customSelect(sqlQuery).get();
I would like to se an api for inner join selects
If you use custom select queries, I'd recommend to get them checked at compile time.
But yeah, moor should support subqueries in its Dart api.
For us the compile time checked option is not possible because we need to do the mapping ourselfs. I have created an extra ticket for tracking that. #615
Subqueries with the Dart API would make the query builder even more powerful. I can see them very useful not ony with joins, but as one value expressions to use as extra columns for a select statement.
SELECT table.*,
(SELECT something from other_table WHERE ...)
FROM table
The moor files can be used for subqueries, but the Dart API can be greatly benefitial for complex parametrized queries where the select and where statements change based on the input.
