Why is ID implicitly included in Select queries ?

[jiaxuan]

I followed the examples to define a simple schema:

type Object struct {
	ent.Schema
}

// Fields of the Object.
func (Object) Fields() []ent.Field {
	return []ent.Field{
		field.String("device").Default(""),
		field.String("bucket").Default(""),
	}
}

However, this simple query produces unexpected result:

entries, err := s.client.Object.
		Query().
		Unique(true).
		Select(object.FieldDevice, object.FieldBucket).
		All(context.Background())

The underlying query turns out to be SELECT DISTINCT objects.id, objects.device, objects.bucket FROM objects. Including id here seems counter-intuitive and is not what Unqiue is supposed to produce in this context.

I'm able to achieve similar effect with GroupBy(object.FieldDevice, object.FieldBucket).Aggregate(...), but I think that comes with a performance penalty compared to SELECT DISTINCT objects.device, objects.bucket. Is there a version of Select that doesn't include ID implicitly?

The testing is done with ent v0.11.3 and sqlite3.

[a8m]

Hey @jiaxuan 👋

That's a good question, and the reason is that All is not a simple select, as it returns Ent nodes/objects that later can be used as follows:

pets, err := client.Pet.
	Query().
	Select(pet.FieldName).
	All(context.Background())

// Update a pet.
pets[0].Update().SetName(1.1).Save(ctx)

// Query associations.
pets[1].QueryOwner().QueryFriends().All(ctx)

// Delete a pet.
client.Pet.DelelteOne(pets[2])

For all the commands above (and there are more), the ID field is necessary.

However, if you just want to select 2 columns without returning Ent nodes/objects, Ent won't load the ID field in this case.

names, err := client.Pet.Query().
	Select(pet.FieldName).
	Strings(ctx)

var v []struct {
	Name string
	Age float64
}

err := client.Pet.Query().
	Select(pet.FieldName, pet.FieldAge).
	Scan(ctx, &v)

I hope it helped.

[jiaxuan]

Hi Ariel,

Thanks for the answer! That worked!

I actually tried this before asking the question here and it didn't get me the result I wanted. The generated query doesn't have DISTINCT. But if I add Unique(true) I got exactly what I wanted.

What tripped me up was I assumed Unqiue(true) was the default behavior and I didn't add it in my initial testing before looking for alternatives. Looks like in this case Unique(true) is required explicitly.

[a8m]

I’m glad to hear it helped 🙂.

For future readers, there’s an example for it in the doc: https://entgo.io/docs/crud#field-selection

[jiaxuan]

This is where I got that false impression from when I was trying to come up with a custom predicate:

https://entgo.io/docs/predicates

Most examples have DISTINCT in the generated queries. After reading it one more time, it's not clear when DISTINCT will be added and I shouldn't have made that assumption :)

[a8m]

After reading it one more time, it's not clear when DISTINCT will be added and I shouldn't have made that assumption

In Ent, DISTINCT is added by default for queries that return nodes/objects because it is not expected to have duplicate nodes even if your JOIN is wrong.

For custom SELECT (e.g. T.Query().Select().Scan(...)) we do not add DISTINCT because different nodes may share identical values.