db.Query with sql join error Scan error on column

Question

error!!!

Scan error on column index 1, name "url": unsupported Scan, storing driver.Value type []uint8 into type *[]handle.Movie

https://gyazo.com/7532a1c3793c892e721054998865609d

https://gyazo.com/278066e6da16f13cd9c56874beb71026

type Movie struct {
    ID         int
    Url        string
    CategoryID uint
}

type Category struct {
    ID     int
    Name   string
    Movies []Movie
}


func Connected() []Category { 
    db := ConnectDB()
    defer db.Close()

    //sql
    query := `SELECT c.name,m.url FROM categories c left join movies m on c.id = m.category_id`

    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err)
    }

    var sli []Category
    var v1 Category   
    for rows.Next() {

        if err := rows.Scan(&v1.Name, &v1.Movies); err != nil {
            log.Fatal(err)
        }

        sli = append(sli, v1)
    }
    fmt.Println(sli[0].Movies)
    return sli
}

I want to achieve this result!!!

[{1 aaa [https//you...,https//you...],2 bbb [https/you...]}]

I want to get a movie that is linked by category association by slice

-----------------------------PS ---------------------------------

This is what I wanted to do!!!

func Connected() []Category {
    db := ConnectDB()
    defer db.Close()

    //sql
    query := `SELECT c.id, c.name, m.id, m.url FROM categories c left join movies m on c.id = m.category_id ORDER BY c.id ASC`
    rows, err := db.Query(query)
    if err != nil {
        log.Fatal(err)
    }

    var sli []Category
    var c Category 

    var m Movie

    for rows.Next() {
        if err := rows.Scan(&c.ID, &c.Name, &m.ID, &m.Url); err != nil {

            log.Fatal(err)
        }
        m.CategoryID = c.ID
        l := len(sli)
        if l > 0 && sli[l-1].ID == c.ID {
            sli[l-1].Movies = append(sli[l-1].Movies, m)

        } else {
            if len(c.Movies) != 0 {
                c.Movies = remove(c.Movies, c.Movies[0])
            }

            c.Movies = append(c.Movies, m)
            sli = append(sli, c) 


        }
    }
    return sli
}



func remove(ints []Movie, search Movie) []Movie {
    result := []Movie{}
    for _, v := range ints {
        if v != search {
            result = append(result, v)
        }
    }
    return result
}

Thanks everyone

Also please note that while the result you want matches the queried columns, it does not match the Category data structure that you've declared. That is, it looks like what you want is each category with a slice of the associated movies' urls, but what you have in the the Category.Movies field is a slice of Movie values, which are structs with more fields than just the Url... So which is it? — mkopriva
– mkopriva, Commented Jan 6, 2020 at 9:11

AmRzA · Accepted Answer · 2020-01-06 09:23:19Z

1

well as I see you are trying to store a single URL into an array of Movie struct which is impossible. your query may return all URLs for each category but each URL is in a single row and you should aggregate them your self. and as I know you should scan data into Golang default types, not your custom structs. mapping your data into your custom struct is a different thing.

this is a sample code but I don't have access to your database so I can't test it but it should work as you want.

type Movie struct {
ID         int
Url        string
CategoryID uint
}

type Category struct {
ID     int
Name   string
Movies []Movie
}


func Connected() []Category { 
db := ConnectDB()
defer db.Close()

//sql
query := `SELECT c.id, c.name, m.id, m.url FROM categories c left join movies m on c.id = m.category_id 
ORDER BY c.id ASC`

rows, err := db.Query(query)
if err != nil {
    log.Fatal(err)
}

var sli []Category
var v1 Category
var m Movie
for rows.Next() {
    if err := rows.Scan(&v1.ID, &v1.Name, &m.ID, &m.Url); err != nil {
        log.Fatal(err)
    }
    m.CategoryID = v1.ID
    l := len(sli)
    if l > 0 && sli[l - 1].ID == v1.ID {
        sli[l - 1].Movies = append(sli[l - 1].Movies, m)
    } else {
        v1.Movies = append(v1.Movies, m)
        sli = append(sli, v1)
    }
}
fmt.Println(sli[0].Movies)
return sli
}

answered Jan 6, 2020 at 9:23

AmRzA

19011 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mkopriva Over a year ago

In your example the indexing logic is relying on the category ids starting at 1 and being sequential without gaps, that is in most cases, if not all, the wrong heuristic.

AmRzA Over a year ago

I think it doesn't matter how are category ids, I sorted result by category ids so I expect to get them in a sequential way.

mkopriva Over a year ago

You're right, I think, I completely misread your code. My apologies.

Collectives™ on Stack Overflow

db.Query with sql join error Scan error on column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related