tink_sql

tink_sql copied to clipboard

I'd like to add a few more functions (COUNT, MIN, MAX, SUM, ...). These operate on multiple rows and can be used while selecting (and in HAVING if we should add it) but not in a WHERE clause. So I'm looking for suggestions on a proper way to disable these from being using in where. (Also I actually sneaked in Functions.count already to complete Dataset.count.)

Just stumbled into a situation where I need to write this SQL:

(basically to sort out the correct chatroom that consist of the exact two members)

SELECT chatroom.id, count(*) AS users FROM chatroom 
LEFT JOIN chatroomMember ON chatroom.id = chatroomMember.chatroom 
WHERE chatroom.type = 'mail' && chatroomMember.user in (0, 4) 
GROUP BY chatroom.id 
HAVING users = 2;

But seems like tink_sql doesn't support GROUP BY and HAVING

We'd need to merge the selected aliases into the Fields after a select to make HAVING useful. But they can't be used in a WHERE clause, only in group by/having/order by. They could be stored elsewhere but it'd make things harder. I guess if we ever get https://github.com/HaxeFoundation/haxe-evolution/pull/46 it would probably be doable without introducing a ton of macros. If you can think of another way forward here, I'm eager to try and implement it :)

Recently I wrote something to sort by an alias, something like this:

db.table.select({sort: table.col1})
  .orderBy(fields -> [{field: cast EField(null, 'sort'), order: Desc}]);

Note that there is no way to reference the alias field in the orderBy clause. Understand that is quite complex to get the field into the type system. How about some temporary workaround such as having a static function to create the field from string (which basically simply returns EField(null, field) but casts to the required Field<T> type)

How about joining the aliases in (with tink_anon?) but throwing a runtime error if used in where?

Ok, just so that I understand, is this correct?

• where clauses can refer to original field names
• having clauses can refer to selected field names

Right. To be overly specific, having may also refer to original field names. Order by and group by behave as having, so at this time they're lacking the selected field names.

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

https://dev.mysql.com/doc/refman/5.6/en/problems-with-alias.html

Well, looks like we'll have to add another type parameter :D

So then we'd have to make orderBy and groupBy macros right? To join the fields and aliases. But they currently don't act like Filter where different tables are presented as function arguments. Not sure how we move the aliases in in that scenario. For having they could be appended to the arguments? Doesn't look so nice :)

having((chatroom, chatroomMember, alias) -> alias.users == 2)

Or would it make more sense to always go with a single object?

having(fields -> fields.users == 2) // {chatroom: ..., chatroomMember: ..., users: 2}

Can we have having which gets the same fields object as orderby/groupby first? So at least we can go on (with hacks like cast EField(null, 'sort'))

I don't see why not ;)

How about adding a method to get the selected fields and continue in a closure where you can use those for order/group/having, passing a subset of Dataset which does not allow where.

db.chatroom
  .leftJoin(db.chatroomMember)
  .on(chatroom.id == chatroomMember.chatroom)
  .select({
    id: chatroom.id,
    users: count()
  })
  .where(
    chatroom.type == 'mail' && chatroomMember.user in 0 ... 4
  )
  .with((selection, query) ->
    query
      .groupBy([chatroom.id])
      .having(selection.users == 2)
  );

Or maybe use the select macro to name it as the last argument in Filter used for order/group/having:

db.chatroom
  .select({
    id: chatroom.id,
    users: count()
  } in selection)
  .having(selection.users == 2); // .having((chatroom, selection) -> selection.users == 2)

Or maybe use the select macro to name it as the last argument in Filter used for order/group/having:

I guess this API looks cleaner. And maybe default the var name to be selection if the EIn expr is omitted.