Generating Queries with Tuples

[diogorodriguesc]

What do you want to change?

I did notice we don't have documentation for tuples, so I think its not implemented yet.

MySQL documentation reference: https://dev.mysql.com/doc/refman/8.4/en/row-constructor-optimization.html

SELECT code, type FROM country_types WHERE (country = "pt" AND type = 1) OR (country = "de" AND type = 3);

with tuples:

SELECT code, type FROM country_types WHERE (country, type) IN (("pt", 1), ("de", 3));

What database engines need to be changed?

MySQL

What programming language backends need to be changed?

No response

[kyleconroy]

Can you include the table definition for country_types?

[diogorodriguesc]

This query was just an example. It doesn't translate the query in which I was trying to implement Tuple using sqlc.
Anyway, it could be:

CREATE TABLE country_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country CHAR(2) NOT NULL,
    code VARCHAR(50) NOT NULL,
    type INT NOT NULL,

    UNIQUE KEY unique_country_type (country, type)
);

Implementing tuple in sqlc would enable it's users to create some more dynamical filtering when they need to filter by a set of conditions with OR:

WHERE (country = "pt" AND type = 1) OR (country = "de" AND type = 3) OR ...

instead they would be able to create using

WHERE (country, type) IN (("pt, 1), ("de", 3), ...)

As an example, the query definition could be:

-- name: GetCountryTypes :many
SELECT *
FROM country_types
WHERE (country, type) IN (sqlc.slice("countryTypes"));

where countryTypes would be an sqlc type:

type CountryTypeSqlcType struct {
  country string
  type int
}

var countryTypes []CountryTypesSqlcType
countryTypes = append(countryTypes, CountryTypeSqlcType{
  country: "pt",
  type: 1,
}
countryTypes = append(countryTypes, CountryTypeSqlcType{
  country: "de",
  type: 3,
}

result, err := queries.GetCountryTypes(ctx, countryTypes)