selecting only specified columns?

[armhold]

Hi, thanks for writing reform.

I'd like to know if it's currently possible to return only a subset of columns, e.g.:

SELECT id, name, email FROM users

... and get back a slice of records with only those particular fields (id, name, email) pulled from the database. I just started with reform, so perhaps I missed it, but I don't see how to do this.

Perhaps this issue is referring to that functionality?

Related, what is the best way to get a count of results (without actually reading rows)? My use case is to find out if a row meeting a particular constraint exists (e.g. is there already a user with this email?)

I already have code for accomplishing both of these using database/sql, but if there is an idiomatic way of doing this with reform that would be handy.

Thanks

[AlekSi]

Hi George,

The issue your referenced is the correct one. It wasn't implemented yet mostly because we did not consider it very important. In our experience, bugs coming from the fact that only some fields of the record were updated are quite frequent, and overhead of receiving all columns is neglectable in most cases. However, the story is very different for column-oriented databases which we want to support, so we plan to implement the referenced issue for v1.4.

Related, what is the best way to get a count of results (without actually reading rows)? My use case is to find out if a row meeting a particular constraint exists (e.g. is there already a user with this email?)

I would recommend doing that without counting rows. For example, if you want to create or update user by email:

var user User
err := q.FindOneTo(&user, "email", "user@example.com")
if err != nil && err != reform.ErrNoRows {
    return err
}
user.Password = newPassword
return q.Save(&user)

[armhold]

Perhaps I'm pre-optimizing, but I have one particularly fat table and am concerned about reading in full records where only a column or two are needed in a few cases.

If I can still fall back to old fashioned SQL queries to handle this, that's fine (and perhaps will avoid bugs like you mentioned.) Is there any issue with using the underlying db connection for such queries once it's been handed to reform.NewDB(), or does reform need to "own" it at that point? Thanks.

[AlekSi]

Perhaps I'm pre-optimizing, but I have one particularly fat table and am concerned about reading in full records where only a column or two are needed in a few cases.

I recommend you to actually measure the difference. Unless it is index-only scan, the speed improvement may be neglectable (it was in my experience).

If I can still fall back to old fashioned SQL queries to handle this, that's fine (and perhaps will avoid bugs like you mentioned.)

Using SQL directly is very much encouraged in reform. We do not plan to make a method for every possible query type, nor do we want to make a query builder – SQL is better for that. Reform provides a few helpers for you there like generated Values() and Pointers() methods, QualifiedColumns() / QualifiedView(), NextRow(), logging, etc.

Is there any issue with using the underlying db connection for such queries once it's been handed to reform.NewDB(), or does reform need to "own" it at that point?

*reform.DB stores *sql.DB inside, but does not own it. You don't even have to pass *sql.DB around, since you can get it back from *reform.DB with DBInterface(). But you also can just use *reform.DB directly – it has Begin() method (and InTransaction() helper), and also embed Querier with normal Query/QueryRow/Exec methods. The only difference between reform.DB.Querier and sql.DB methods is logging.

[armhold]

Using SQL directly is very much encouraged in reform. We do not plan to make a method
for every possible query type, nor do we want to make a query builder – SQL is better for that

I agree. Thanks for your guidance.

[AlekSi]

You are welcome!

[AlekSi]

I created #144 to add more helpers for working with SQL. Please comment on it if you think something else would be useful.

[armhold]

As a new user, I'm still not sure what is the intended usage of a Struct vs a Record. I have scanned the docs a few times trying to figure that out, but the smallest hint I found was:

There should be zero pk fields for Struct and exactly one pk field for Record

From that statement I get the impression that perhaps a Struct is a data structure for partial results (not a full DB row record), but I am still unclear.

If I could offer some constructive criticism: more docs with a "big picture" explanation and then specific examples would go a long way.

[AlekSi]

Struct was created for views. In most cases, you want to use Record.