Does Jet support QueryRow?

[duchiporexia]

Does Jet support QueryRow for only one row.

[go-jet]

QueryRow isn't part of jet API but it is possible to retrieve raw sql with arguments from the jet statement and call it manually.

query := SELECT(...)
sql, args := query.Sql()

row := db.QueryRow(sql, args...)              // db can be sql.DB, sql.Tx ....

This also applies for the other methods from "database/sql".

---

Query into slice

Query method returns empty slice when there is no rows in a result set, so it can be used to check if there are any rows in a result set.

dest := []model.Film{}
err := query.Query(db, &dest)
if len(dest) == 0 {
    // no rows in result set
}

---

Query into struct

There is also an option to pass a single struct pointer to Query method:

dest := model.Film{}
err := query.Query(db, &dest)

This is not the same as QueryRow, because in a case when destination struct contains slices, multiple rows can be mapped into single struct destination.

Currently in a case of empty result set, it doesn't returns an error, but most likely it should.
I've created new branch to handle this issue err-no-rows. In a case of empty result set sql.ErrNoRows is returned. This solution is still work in progress and it might change completely.

---

Also note that Query into a slice will also return empty slice if destination doesn't match any column from result set. Query into struct will return sql.ErrNoRows on err-no-rows branch if destination is unsuitable. This problem will be most likely handled in a feature release by Query method panicking if it can not map at least one result set column into at least one destination field.

---

All this will be part of the new release v.2.2.0.

[duchiporexia]

@go-jet Great!
But in Go sql, "Query" doesn't return sql.ErrNoRows, although "QueryRow" does.

So, probably, we need two versions: error and no error for different use cases.

For those who are familiar to Go sql, it is better to add an extra QueryRow function instead of returning an error in "Query into struct", which seems confusing to them.

[go-jet]

More appropriate name for Jet Query would be QueryResultMapping. Because it does 3 things beneath. It calls "database/sql" Query, then it calls Scan on each row and maps each row scan result into destination. The idea behind Jet Query method wasn't to wrap just "database/sql" Query but to wrap those 3 steps and thus speed up the development.

Also neither "database/sql" Query or QueryRow returns sql.ErrNoRows. Only each Row's Scan will return ErrNoRows if row is empty.

Even if there is a separate method in Jet, that will query into struct, it wouldn't be called QueryRow, it would be probably called something like QueryOne, because multiple rows can be scanned into one struct destination, if that struct destination contains slices. If the struct destination does not contain slices than this imaginary QueryOne method would behave as QueryRow.
All this is already a part of jet Query(of course sql.ErrNoRows is now missing).

If the idea behind new QueryRow method is to have access to sql.Row and manually call Scan then developer can use statement Sql() method to get raw sql with arguments as described above.
This can be achieved with only one additional line of code.

[duchiporexia]

@go-jet Yes, you are right. Jet should use another query (QueryOne or QueryResultMapping) to return the error ErrNoRows, not in Query.

[go-jet]

Whether there will be two methods QueryResultMapping and QueryRestulMappingOne or just one Query(QueryResultMapping) is just a a matter of preference.
At this moment there is no need to make a such a big API breaking change. If there is enough reason to split Query into two methods it will have to wait until version 3.0.0.

[duchiporexia]

@go-jet Good. Make sense. Thanks.

Sorry, this issue is related to the Version 2.2.0 Milestones . So, I cannot close it. Reopen it again.