Comments (7)
In the code I found the option FlattenOption.flattenAndSetToNull
. It can be used to fix the example given above. But it is a little hard to get (not exported in index.ts), needs to be repeated in every query, and does not actually work as I'd like.
What I would expect is that the property is set to null
if the relation does not exist. But if the relation does exist, but you happen to only select columns that have value null
, an object containing nulls is fine.
I suppose this is a little difficult. A way to do this would be to always select the primary key, and set the property to null if the primary key is null in the resulting row.
from typed-knex.
Hi Allard,
Great to see you're looking into this again :)
You pinpointed the only thing that I'm not happy with. (The rest works great 😄)
The big problem is that it's hard to decide if a relation should be set to null.
For example
SELECT users.id as 'id', groups.name as 'group.name' FROM users OUTER JOIN groups ON users.groupId = groups.id
If groups.name is nullable, than we have no way to know if the property group is null, or the name property of group is null ( { id: string, group: {name: null|string}}
or {id:string, group: null | {name:null|string}}
)
We could automatically add groups.id to the SELECT, but I want to avoid adding implicit magic.
My current idea for v3 is a follows: if an INNER JOIN is used (also on nullable properties), the type of the result will be always be not nullable.
Eg
SELECT users.id as 'id', groups.name as 'group.name' FROM users INNER JOIN groups ON users.groupId = groups.id
Will result in { id: string, group: {name: null|string}}
And for OUTER JOIN's, an extra parameter is added to the outer join functions, where you can specify which column determines the nullability
So typedKnex.query(User).leftOuterJoinColumn(i => i.group).select(i=>[i.id, i.group.name]);
will result in in { id: string, group: {name: null | string}}
and typedKnex.query(User).leftOuterJoinColumn(i => i.group, g=>g.name).select(i=>[i.id, i.group.name]);
will result in in { id: string, group: null |{name: null | string}}
I think a runtime check can help, so typedKnex.query(User).leftOuterJoinColumn(i => i.group, g=>g.id).select(i=>[i.id, i.group.name]);
will throw an error, because group.id is not selected.
What do you think of this approach?
BTW
public group: Group | null; // correct typing, but crashes the query builder
Runtime this type is passed to the decorator as Object
instead of Group
, so I had to choose Group?
from typed-knex.
Hi Wouter,
Thanks for your answer. I've been thinking a bit more about this over the past few days and my conclusion is mostly in line with what you commented.
-
The typing of the entity-class is not that relevant. What is relevant is the type of the result of the query. That type is dependant on the things you do in the query, so I think anything that works in the entity is fine. An option would be to use a different decorator, or give the
@Column
a parameter if that is useful for determining the return type. -
For inner joins there is actually no problem at all. The current version will always give the correct result and type: in the result the relation is always non-nullable, and if you only select a nullable column a result like
group: { name: null }
is correct. -
For left outer joins your suggestion sounds fine, but I'd also be happy with a simpler solution. Why would anyone do a left join on a relation that is not nullable? So you could say a left outer join always results in a nullable value. That would not be 100% accurate but good enough in my opinion, and I expect much simpler to build.
It would even make it simpler during use: instead of always having to think about the column to pass in the seconds parameter, the field of the left join would just be nullable.
I have been trying to build a prototype for this, but typed knex is not an easy project to get started on :-)
from typed-knex.
I'd love to make a simple default that just works. But I'm not sure you're solution would work all of the time.
For example if users.groupId and groups.name are both nullable, then typedKnex.query(User).leftOuterJoinColumn(i => i.group).select(i=>[i.id, i.group.name])
/ SELECT users.id as 'id', groups.name as 'group.name' FROM users OUTER JOIN groups ON users.groupId = groups.id
Can give this result:
id | group.name |
---|---|
1 | NULL |
Does that mean {id:1, group: null}
or {id:1: group: { name: null }}
?
from typed-knex.
That would be great!
I would say:
- inner join gives a non-null type:
{ group: Group }
- inner join does not reduce to null:
{ group: { name: null }}
- left join gives a nullable-type:
{ group: Group | null }
- left join reduces to null if all values are null:
{ group: null }
This makes inner join work 100% correct and also the typing for the outer join. The result for outer joins will not always be correct, as you described, if you only select nullable columns. But I think it would be a great improvement of the current situation where outer joins are almost always wrong, both in typing and in result.
The gap that will be left is small enough to accept in my opinion. And you can always iterate on it, for example by making the criteria for reducing to null configurable in a second parameter.
from typed-knex.
Just to keep you posted: I started work on this in #11
leftOuterJoinTableOnFunction
works and I also added some comments to help my future self 😃
leftOuterJoinColumn
is a bigger issue, because select
only uses type information from the initial model. Foreign key properties that are optional, are handled as non-optional.
I see two solutions.
The first one is to try to change the model. If an inner join is made, change the property to NotNullable<Model<P>>
, and if an outer join is made, change the property to Model<P> | null
The other solution would be to have a model and a 'select model' The model is the class that you give to the query, including all foreign key objects. The select model only has the non foreign key objects.
Only after when something is joined, is it available in the select model.
The first one feels a bit like a hack. An added benefit to the second one is that it prevents you from adding columns from tables to the select clause which aren't joined yet. It does mean that you have to write joins before select, which is not how normal SQL is written.
from typed-knex.
Great!
I don't think changing the model is a hack: doing tings like joins or selects does change the type of the result. In a system like TypedKnex the Entity class is basically only a description or configuration for TypedKnex, not a real class like in a classic ORM.
But the second options sounds fine as well. It does indeed have some downsides. The order that you have to do selects and joins is different from SQL but makes sense in a programming language like JS. In code I always do the selects last.
Anther downside is that you cannot do things like:
const q = typedKnex.query(...);
q.leftOuterJoinColumn(...);
q.select(...);
const r = q.getFirst();
This can be fixed most of the time by either chaining or reassigning to the variable of course. When dynamically building the query
from typed-knex.
Related Issues (20)
- Select all aliases bug HOT 2
- TypeError when using innerJoinColumn HOT 2
- Left join throws error HOT 4
- Get ID after inserting row HOT 13
- Some property names are incomplete after a query
- .singleOrNull without .select does not show | null typing HOT 3
- MS Code Freezing with Typed Knex but fine without HOT 2
- Using andOn inside innerJoinTableOnFunction not translated column according to definition HOT 5
- TypeError: Cannot read properties of undefined (reading 'tableName') HOT 4
- Returning values with useKnexQueryBuilder HOT 2
- Can I use `forUpdate` in `typed-knex` ? HOT 2
- Composite key considerations HOT 1
- updateItemWithReturning ignores previous modifications to querybuilder HOT 6
- getCount with postgresql returns string, not number HOT 4
- ValidateTables with multiple databases HOT 2
- Select autocomplete dropdown breaks with TypeScript v4.7.x HOT 4
- Table decorator is incorrectly marked as deprecated HOT 2
- Extending Tables HOT 1
- Wrong table alias in "whereColumn" clause with tables from separate PostgreSQL schemas HOT 1
- Support ‘unknown’ columns HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from typed-knex.