-
Notifications
You must be signed in to change notification settings - Fork 114
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[Question] How to populate nested "entities" that require self-join #252
Comments
Anyway, as a side-note, I tried the second approach directly into a sample DB and the performance of that query is just terrible. I may need to evaluate a different approach about how to populate this. |
Yeah, you'll need to be careful with the number of joins. Although the number of joins is not limited with jet, the performance can suffer if the query returns a huge number of rows. One possible way to do it is to have 2 queries. In the first query, you fill type component struct {
model.Components //<-- first query
Vulnerabilities []model.Vulnerabilities //<-- first query
Children []component //<-- second query
} If var dest component
err := stmt1.Query(db, &dest)
err := stmt2.Query(db, &dest.Children) |
Thanks for the suggestion, but I cannot do the query you proposed since I need to map the results to a slice of components (I should have made that clear from the start). Like this: components := []*component{}
if err := stmt.Query(db, &components); err != nil {
return nil, err
} And, if I resolve it naively, it results in the N+1 problem. |
Well, I came up with a query that returns the correct information, but I don't know how to map it using the QRM: stmt:= postgres.SELECT(
table.Components.AllColumns,
table.Vulnerabilities.AllColumns,
tableChildren.AllColumns,
tableChildrenVulnerabilities.AllColumns,
).FROM(
table.Components.
LEFT_JOIN(
table.ComponentVulnerabilities,
table.ComponentVulnerabilities.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
table.Vulnerabilities,
table.Vulnerabilities.ID.EQ(table.ComponentVulnerabilities.VulnerabilityID),
).
LEFT_JOIN(
table.ComponentChildren,
table.ComponentChildren.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
tableChildren,
tableChildren.ID.EQ(table.ComponentChildren.ChildID),
).
LEFT_JOIN(
tableChildrenComponentVulnerabilities,
tableChildrenComponentVulnerabilities.ComponentID.EQ(tableChildren.ID),
).
LEFT_JOIN(
tableChildrenVulnerabilities,
tableChildrenVulnerabilities.ID.EQ(tableChildrenComponentVulnerabilities.VulnerabilityID),
),
) In SQL (simplified version with just IDs): SELECT components.id AS "components.id",
vulnerabilities.id AS "vulnerabilities.id",
children.id AS "children.id",
children_vulnerabilities.id AS "children_vulnerabilities.id"
FROM components
LEFT JOIN component_vulnerabilities ON (component_vulnerabilities.component_id = components.id)
LEFT JOIN vulnerabilities ON (vulnerabilities.id = component_vulnerabilities.vulnerability_id)
LEFT JOIN component_children ON (component_children.component_id = components.id)
LEFT JOIN components AS children ON (children.id = component_children.child_id)
LEFT JOIN component_vulnerabilities AS children_component_vulnerabilities ON (children_component_vulnerabilities.component_id = children.id)
LEFT JOIN vulnerabilities AS children_vulnerabilities ON (children_vulnerabilities.id = children_component_vulnerabilities.vulnerability_id); |
Aha, in that case your destination would need to have a separate type for children. For instance: type component struct {
model.Components
Vulnerabilities []model.Vulnerabilities
Children []struct{
model.Components `alias:"children"`
Vulnerabilities []model.Vulnerabilities `alias:"children_vulnerabilities"`
}
} You can't use the same |
That worked! This library rocks!
Yes, I'm aware of this, thank you. |
Sorry to re-open this... Here's the thing: I thought it worked, but using the query I shared above, it only fills the first vulnerability for each child. For what I understand, given that my query returns something like:
then, using the following structs: type component struct {
model.Components
Vulnerabilities []model.Vulnerabilities
Children []child
}
type child struct {
model.Components `alias:"children"`
Vulnerabilities []model.Vulnerabilities `alias:"children_vulnerabilities"`
} the QRM should populate the structs to something like: {
"components": [
{
"id": "component_00",
"vulnerabilities": [
{
"id": "vulnerability_00"
},
{
"id": "vulnerability_01"
},
{
"id": "vulnerability_02"
},
{
"id": "vulnerability_03"
}
],
"children": [
{
"id": "component_01",
"vulnerabilities": [
{
"id": "vulnerability_11"
},
{
"id": "vulnerability_12"
}
]
},
{
"id": "component_02",
"vulnerabilities": [
{
"id": "vulnerability_21"
}
]
}
]
}
]
} Am I doing something wrong? Is there a more efficient way to debug the QRM? |
Hi @itaranto , type child struct {
model.Components `alias:"children"`
Vulnerabilities []struct {
model.Vulnerabilities `alias:"children_vulnerabilities"`
}
} |
Well, I'm glad this is a bug then... I tried your branch and it solved my issue in regards to only populating the first vulnerability for each children. Now, there's another similar issue this The problem was the following: My guess is these two issues are basically the same. |
Basically, a bug is if you have two model slices of the same type in your destination, and one slice is aliased then qrm would not populate destination correctly: type struct Dest {
....
TList []model.T
....
TList2 []model.T `alias:"t_alias"`
....
}
// if insetead TList2 []model.T `alias:"t_alias"` a new struct is used,
// like TList2 struct{ []model.T `alias:"t_alias"` } then it's not an issue This usually requires, like in your case, self join and 2 N joins on the same level, which is not a common case, and probably a reason why it hasn't been detected so far. |
Could you share your destination? It would be easier to confirm. |
The destination struct is the same as in my previous comment. My point was, about two (seemingly) different issues that got solved by this fix:
|
Fixed with Release v2.10.1. |
I have the following models:
And this is the struct where I want the data to be mapped to:
Notice the
Children
field refers to itself :component
instead ofmodel.Components
because I want children to have the same data as the parent, in this case, theVulnerabilities
.If I didn't want to populate the children's sub-relations, I could have done something like this:
And then make a query like this:
In order to get the children with its sub-entities, I know that I need to do a sub-query but I'm not sure if the QRM supports this.
I tried something like this:
But I can't make the mapper to fill the structs in the way I need it.
The text was updated successfully, but these errors were encountered: