Making recent posts at blog programming

Getting Help
1 
	eg.GET("/", func(c *gin.Context) {
		query := "SELECT id, r_id, p_id, s_id from whole ORDER BY id DESC limit 6"
		r, err := db.Query(query)
		if err != nil {
			log.Fatalln("DB Connetion ERROR occured :", err)
		}
		data := []PRS_id{}
		for r.Next() {
			var temp PRS_id
			r.Scan(&temp.ID, &temp.RID, &temp.PID, &temp.SID)
			data = append(data, temp)
		}
		fmt.Println(data)
		c.HTML(http.StatusOK, "index.html", data)
	})

I’m making my own blog and there are three category : projects, review, study
I made each category’s table in my DB, and also made ‘whole’ DB which has foreign key with each categories to make viewing ‘recent 6 posts’
the number of posts are 9
In my thought, the ‘data’ structure has to print
{9,3,0,0}{8,0,3,0}{7,3,0,0}{6,0,0,3}{5,0,0,3}{4,0,3,0}
but it actually prints
{9,3,0,0}{8,0,0,0}{7,3,0,0}{6,0,0,0}{5,0,0,0}{4,0,0,0}

the id and PID printed well, but Idk why RID and SID didn’t print well
and when I write that query in DB at console, it works well.
anyone knows about this problem?

2

Hi @choigonyok,

  • How is the type PRS_id defined?
  • What data types do the columns id, r_id, p_id, and s_id have?
  • And most importantly, what error value does r.Scan() return?
3

Hello!

first, PRS_id defined like this

type PRS_id struct {
	ID	int // 	'whole' table's primary key = id column
	PID     int //	'projects' table's primary key = p_id column
	SID	int //	'study' table's primary key = s_id column
	RID	int //	'review' table's primary key = r_id column
}

→ I defined this to get ‘whole’ table’s data.

second, all the columns has INT type
here’s the mysql result of executing that query in terminal.
±-----±-----±-----±-----+
| id | p_id | r_id | s_id |
±-----±-----±-----±-----+
| 1 | 1 | NULL | NULL |
| 2 | NULL | NULL | 1 |
| 3 | 2 | NULL | NULL |
| 4 | NULL | 1 | NULL |
| 5 | NULL | 2 | NULL |
| 6 | NULL | NULL | 2 |
±-----±-----±-----±-----+

and third,
there’s no returned error, but the result is quite different from my expectation as I wrote.
so that I asked for some help!

4

Just to get sure we’re talking about the same, did you change your code to handle the error returned by r.Scan()? And the error that r.Scan() returns is always nil?

I am asking because the only error handling I see in your code is for db.Query().

Looking at the direct query result, I would assume that the issue is related to handling NULL values. Go has no concept of SQL NULL, and there are various ways of bridging that semantic gap (see e.g. this nice summary: Ways to handle SQL NULL values in Go | Ekin Karadeniz.

Maybe try using COALESCE in the SELECT statement:
SELECT id, COALESCE(r_id, 0), ....

1 Like
5

Adding to what @christophberger is saying, change this:

r.Scan(&temp.ID, &temp.RID, &temp.PID, &temp.SID)
data = append(data, temp)

… to this:

if err = r.Scan(&temp.ID, &temp.RID, &temp.PID, &temp.SID); err != nil {
	log.Fatalln("Problem scanning:", err)
}
data = append(data, temp)

Also I’m a little confused about the data because in your example of what you want to see for the overlapping IDs in your table and code, it’s this in your first comment:

{6,0,0,3}{5,0,0,3}{4,0,3,0}

But if you look at the table you posted you can see that’s not what the raw query returned:

| 4 | NULL | 1 | NULL |
| 5 | NULL | 2 | NULL |
| 6 | NULL | NULL | 2 |

Wouldn’t you actually expect to see the following?

{6,0,0,2}{5,0,2,0}{4,0,1,0}

Anyway, I suspect Christopher is correct that null values are the problem. Once you change the line of code above I suspect you’ll see an error. Another solution would be to look at sql.NullInt64:

The ergonomics of sql.Null are slightly clunky IMO (specifically doing things like serializing to JSON) so I usually go the pointer route myself. Or write custom marshal/unmarshal for the sql.Null types I want to use.

1 Like
6

I added COALESCE and it’s solved!!
I got to know COALESCE() and Go can’t handle NULL.
It was really helpful for me.
Thx a lot @christophberger !! :):slight_smile:

1 Like
7

Yes, I think the table has been slightly changed while testing, and it was really helpful! Thank you :slight_smile:

8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.