Sqlx: Need guidance on proper use of rows.StructScan() for a "has many" join

[johnknapp]

This is a fantastic package but I must misunderstand how to use rows.StructScan() for a has many join.

My database features a typical has many relation where my element_anchors table has element_id FK pointing to the PK in elements table. Each Element record has a handful of ElementAnchor records.

Models:

type Element struct {
    Cmod           string          `json:"cmod"`
    Name           string          `json:"name"`
    IDescription   string          `json:"description" db:"i_description"`
    ElementAnchors []ElementAnchor `json:"anchors"`
}

type ElementAnchor struct {
    Statement string `json:"statement"`
    Level     int    `json:"level"`
    ElementID int    `json:"-" db:"element_id"`
}

Relevant query stuff:

sqlStatement := `SELECT
e.cmod, e.name, e.i_description, ea.statement, ea.level
FROM elements e
    INNER JOIN element_anchors ea
    ON e.id = ea.element_id
ORDER BY e.cmod;`

rows, err := db.Queryx(sqlStatement)

Relevant row scanning stuff:

elements := make([]Element, 0)
for rows.Next() {
    var e Element
    err = rows.StructScan(&e)
    if err != nil {
        log.Fatalf("Scan error: %s\n", err)
    }
    elements = append(elements, e)
}

To clarify, my objective is this JSON structure:

[{
"cmod": "a cmod",
"name": "a name",
"description": "a description",
"anchors": [{
    "statement": "a statement",
    "level": 42
    }, {
    "statement": "another statement",
    "level": 84
    }]
},{
 <more like the above>
}]

I've searched open and closed issues and studied the Illustrated Guide to SQLX but have not found sufficient guidance for what I imagine is a common use case. I am unsure if my problem is within the Models, the query or within the StructScan or??

[yousseftelda]

The resulting SQL rows will look like:

|  cmod  |  name  |  description   | statement   | level |
| a cmod | a name | a description  | a statement |   42  |

Your struct will have to match this format, you can achieve this by embedding:

type CombinedElement struct {
   Element
   ElementAnchor
}

One recommendation is to use the canonical name of the column, i.e. table_name.column_name during joins to avoid ambiguity in case two or more tables have a column with the same name:

SELECT 
  e.cmod AS 'element.cmod',
  e.name AS 'element.name',
  e.i_description AS 'element.i_description',
  ea.statement AS 'anchor.statement',
  ea.level AS 'anchor.level',
FROM elements e
JOIN elements_anchors ea
  ON e.id = ea.element_id
ORDER BY e.cmod;

Then you would use this prefixes as the db tag in the combined struct:

type CombinedElement struct {
   Element       `db:"element"`
   ElementAnchor `db:"anchor"`
}

You can scan the result rows into a slice of CombinedElement, then map the result into an Element by collecting all anchors of an element:

var rows []CombinedElement
// query and scan into rows here

// Collect all anchors belonging to the same element in a single slice
anchorsByElementID := make(map[int][]ElementAnchor, 0)
for _, row := range rows {
   anchorsByElementID[row.Element.ID] = append(anchorsByElementID[row.Element.ID], row.ElementAnchor)
}

// construct our actual result
var res []Element
for _, row := range rows {
  row.Element.ElementAnchors = anchorsByElementID[row.Element.ID]
  res = append(res, row.Element)
}

You could probably do it in a single pass over rows by indexing pointers to Element.

[johnknapp]

Thank you very much @yousseftelda for the response, however I can't make this work.

I've added the third struct as recommended:

type Element struct {
    Cmod         *string `json:"cmod"`
    Name         *string `json:"name"`
    IDescription *string `json:"description" db:"i_description"`
}

type ElementAnchor struct {
    Statement string  `json:"statement"`
    Level     float64 `json:"level"`
}

type CombinedElement struct {
    Element       `db:"elements"`
    ElementAnchor `db:"element_anchors"`
}

I've reformatted the query:

_(My table names are plural, lower case)_

    sqlStatement := `SELECT
        e.cmod AS "elements.cmod", 
        e.name AS "elements.name", 
        e.i_description AS "elements.i_description", 
        ea.statement AS "element_anchors.statement", 
        ea.level AS "element_anchors.level"
    FROM elements e
    JOIN element_anchors ea
        ON e.id = ea.element_id
    ORDER BY e.cmod;`

I've initialized the variables as per your response and issued the query:

    var rows []CombinedElement
    var err error

    rows, err = db.Queryx(sqlStatement)

Unfortunately, I have a complier error:

cannot assign *sqlx.Rows to rows (type []CombinedElement) in multiple assignment

As you stressed the order of the 5 rows across the two tables aligns. Accordingly, I'm unsure what to do with this multiple assignment error.

[yousseftelda]

db.Queryx returns sql Rows, what we want is to map the sql Rows into our slice of CombinedElement, to achieve this you can use:

var rows []CombinedElement
if err := db.Select(&rows, sqlStatement); err != nil {
  // handle err
}

// rest of the logic

db.Select is doing a db.Queryx under the hood, followed by scanning the result into the passed pointer.

[johnknapp]

Success!

Thank you again @yousseftelda for the kind assistance. Your inputs, plus a couple of lessons learned have helped me wrap my head around how to handle has many joins with SQLX. In case this can help others, here are some basic rules of thumb I'll use going forward:

Lessons I learned include:

• The query rows returned must align with the three structs (pretty obvious!)
• db.Select does a great job of filling the "parent struct" providing a set of scannable rows. (Thank you @jmoiron, I see now the fantastic power of StructScan!)
• The rows which are scanned must include necessary PK and FK in order for SQLX to properly associate related records. (This was key for me:, I needed to add Element.ID and ElementAnchor.ElementID to the query in order to properly map the ElementAnchor rows to the Element.)

The Stucts with the addition of PK, FK:

type Element struct {
    ID           int     `json:"-"`
    Cmod         *string `json:"cmod"`
    Name         *string `json:"name"`
    IDescription *string `json:"description" db:"i_description"`
    ElementAnchors []ElementAnchor `json:"anchors"`
}

type ElementAnchor struct {
    Statement string  `json:"statement"`
    Level     float64 `json:"level"`
    ElementID int     `json:"-" db:"element_id"`
}

type CombinedElement struct {
    Element       `db:"elements"`
    ElementAnchor `db:"element_anchors"`
}

The revised query (with PK, FK) to produce rows that align with the above:

    sqlStatement := `SELECT
        e.id AS "elements.id", 
        e.cmod AS "elements.cmod", 
        e.name AS "elements.name", 
        e.i_description AS "elements.i_description", 
        ea.statement AS "element_anchors.statement", 
        ea.level AS "element_anchors.level",
        ea.element_id AS "element_anchors.element_id"
    FROM elements e
    JOIN element_anchors ea
        ON e.id = ea.element_id
    ORDER BY e.cmod;

Having added the Element.ID and ElementAnchor.ElementID, the two stage scanning as you outlined worked perfectly. Viewing the relevant source, I see the role StructScan plays. Good stuff!

[johnknapp]

An update: The result was containing duplicate Elements.

With the two loops, each Element is added to result multiple times.

The desired result is:

• Element 1, (Anchors 1.1 , 1.2, 1.3)
• Element 2, (Anchors 2.1, 2l2, 2.3)
• ...

But the result has duplicated Elements:

• Element 1, (Anchors 1.1 , 1.2, 1.3)
• Element 1, (Anchors 1.1 , 1.2, 1.3)
• Element 1, (Anchors 1.1 , 1.2, 1.3)
• Element 2, (Anchors 2.1, 2l2, 2.3)
• Element 2, (Anchors 2.1, 2l2, 2.3)
• Element 2, (Anchors 2.1, 2l2, 2.3)
• ...

The solution was checking for duplicates and conditionally appending:

    duplicateChecker := make(map[int]interface{}) // prepare to prevent duplicate Elements
    var res []Element
    for _, row := range rows {
        row.Element.ElementAnchors = anchorsByElementID[row.Element.ID] 

        // if we have not seen this Element, add it to our result
        if _, exists := duplicateChecker[row.Element.ID]; !exists { // this ID doesn't exist, add it to res.
            // add the element to the map
            duplicateChecker[row.Element.ID] = struct{}{} // set up for next time through the loop
            res = append(res, row.Element)
        }
    }