Using Enums in a Query

[safaci2000]

Here is a the SQL I'm trying to generate for my table.

SELECT buckets.bucket_id AS "getbucketpolicybyenvrow.bucket_id",
     bucket_policy.policy_id AS "getbucketpolicybyenvrow.policy_id",
     buckets.environment_name AS "getbucketpolicybyenvrow.environment_name",
     buckets.create_bucket AS "getbucketpolicybyenvrow.create_bucket",
     buckets.bucket_name AS "getbucketpolicybyenvrow.bucket_name",
     bucket_policy.policy_name AS "getbucketpolicybyenvrow.policy_name",
     bucket_policy.duration AS "getbucketpolicybyenvrow.duration",
     bucket_policy.prefix AS "getbucketpolicybyenvrow.prefix"
FROM storage.buckets
     INNER JOIN storage.bucket_policy ON (bucket_policy.bucket_id = buckets.bucket_id)
WHERE buckets.environment_name = 'production';

The code I am trying to use to mimic this pattern is below. (My struct is named GetBucketPolicyByEnvRow hence the prefix in the alias, if anyone knows how to avoid that it would be great as well)

	stmt := SELECT(
		storageSchema.Buckets.BucketID.AS("getbucketpolicybyenvrow.bucket_id"),
		storageSchema.BucketPolicy.PolicyID.AS("getbucketpolicybyenvrow.policy_id"),
		storageSchema.Buckets.EnvironmentName.AS("getbucketpolicybyenvrow.environment_name"),
		storageSchema.Buckets.CreateBucket.AS("getbucketpolicybyenvrow.create_bucket"),
		storageSchema.Buckets.BucketName.AS("getbucketpolicybyenvrow.bucket_name"),
		storageSchema.BucketPolicy.PolicyName.AS("getbucketpolicybyenvrow.policy_name"),
		storageSchema.BucketPolicy.Duration.AS("getbucketpolicybyenvrow.duration"),
		storageSchema.BucketPolicy.Prefix.AS("getbucketpolicybyenvrow.prefix"),
	).FROM(storageSchema.Buckets.
		INNER_JOIN(storageSchema.BucketPolicy, storageSchema.BucketPolicy.BucketID.EQ(storageSchema.Buckets.BucketID))).
	WHERE(storageSchema.Buckets.EnvironmentName.EQ(String(envName)))
	ORDER_BY(storageSchema.Buckets.BucketName, storageSchema.Buckets.EnvironmentName, storageSchema.BucketPolicy.PolicyName)

The issue is that the environment_name is an enum, and the code that is generated has the following where condition:

WHERE buckets.environment_name = $1::text

which is invalid, as now we are comparing an enum to a string. The table definition has environmentName as a string:

	EnvironmentName postgres.ColumnString

while the model is a valid enum data type.

	EnvironmentName Environment `json:"environment_name" db:"buckets.environment_name"`

I found a work around where I use this instead in the Where clause:

		WHERE(RawBool("buckets.environment_name = $1::storage.environment", RawArgs{"$1": enumVal.String()}))

Is this expected behavior, or am I doing something wrong and I should open up an issue?

[houten11]

If you have enums in your schema, generator will generate enum package for you. After importing the package, you can write:

WHERE(storageSchema.Buckets.EnvironmentName.EQ(enum.Enviroment.Production))

[safaci2000]

That works, though for dynamic queries it would nice to have a mechanism on the Enum that will create the enum from a valid String.

I have the pattern below that's a model file and a string

	var enumVal storageModel.Environment
	err := enumVal.Scan(envName)

Then the enum which contains the 3 enum values, where you'd have to use something along the lines of:

		WHERE(storageSchema.Buckets.EnvironmentName.EQ(NewEnumValue(enumVal.String())))

It would be nice to have the ability similar to the enumVal in the data model, but maybe that's intentional for a reason that escapes me?