Assign an alias to a value column

[scull7]

First, thank you, the library is awesome.

Second, I'm having difficulty understanding how to add an alias to an aggregate column. Specifically when using the aggregate_to_string function (as seen below).

    Select::from_table("employee")
        .column(("employee", "id"))
        .column(("employee", "created"))
        .column(("employee", "updated"))
        .column(("employee", "deleted"))
        .column(("employee", "casino_id"))
        .column(("employee", "uuid"))
        .column(("employee", "name_first"))
        .column(("employee", "name_last"))
        .column(("employee", "title"))
        .value(aggregate_to_string(Column::from(("role", "display"))))
        .inner_join(join_credential)
        .inner_join(join_credential_role)
        .inner_join(join_role)
        .group_by(Column::from(("employee", "id")))

Is it possible to assign an alias to the .value(aggregate_to_string(Column::from(("role", "display"))))?

[pimeys]

Yeah. I was hit by the same problem once, and what I found out it would be nice if that function returns a Function type directly. How you can do it now is Function::from(aggregate_to_string(Column::from(("role", "display")))).alias("foo"). What kind of sucks in the current api is how the tuples are by-default table definitions, leading to the Column::from syntax, and how the function functions (sic) return something that is not a Function enum, and don't allow you to alias them directly.

This is something we should think about for the upcoming 0.2 release.

[pimeys]

https://docs.rs/quaint/0.2.0-alpha.9/quaint/ast/struct.Function.html

[pimeys]

Also some examples would be super cool before we kick the 0.2 out from the door. Ping @tomhoule let's do that soon :)

[scull7]

Thank you for pointing me in the right direction. I just was able to go back and change the code to use quaint instead of my SQL file.

        .value(
            Function::from(aggregate_to_string(Column::from(("role", "display"))))
                .alias("assigned_roles"),
        )

Worked beautifully.

Select::from_table("employee")
        .column(Column::from(("employee", "id")))
        .column(Column::from(("employee", "created")))
        .column(Column::from(("employee", "updated")))
        .column(Column::from(("employee", "deleted")))
        .column(Column::from(("employee", "casino_id")))
        .column(Column::from(("employee", "uuid")))
        .column(Column::from(("employee", "name_first")))
        .column(Column::from(("employee", "name_last")))
        .column(Column::from(("employee", "title")))
        .value(
            Function::from(aggregate_to_string(Column::from(("role", "display"))))
                .alias("assigned_roles"),
        )
        .inner_join(
            "credential".on(("employee", "uuid")
                .equals(Column::from(("credential", "user_uuid")))
                .and(Column::from(("credential", "deleted")).equals(0))),
        )
        .inner_join(
            "credential_role".on(("credential", "id")
                .equals(Column::from(("credential_role", "credential_id")))
                .and(Column::from(("credential_role", "deleted")).equals(0))),
        )
        .inner_join(
            "role".on(("credential_role", "role_id")
                .equals(Column::from(("role", "id")))
                .and(Column::from(("role", "deleted")).equals(0))),
        )
        .group_by(Column::from(("employee", "id")))

[scull7]

Should I close this issue or leave it open to track adding examples?

[pimeys]

In the version v0.2.0-alpha.11 the function interfaces to all but the row_number are a bit easier:

.value(aggregate_to_string(("role", "display"))).alias("assigned_roles")

... should work.

[pimeys]

The problem is the Aliasable trait that is mainly implemented for the Table and the &str/(&str, &str) etc. that will turn into Table. This would clash with Column, and I don't really know which one we should choose.

So we could have:

impl<'a> Aliasable<'a> for (&'a str, &'a str) {
    type Target = Table<'a>;

    fn alias<T>(self, alias: T) -> Self::Target
    where
        T: Into<Cow<'a, str>>,
    {
        let table: Table = self.into();
        table.alias(alias)
    }
}

which is the current state, or

impl<'a> Aliasable<'a> for (&'a str, &'a str) {
    type Target = Column<'a>;

    fn alias<T>(self, alias: T) -> Self::Target
    where
        T: Into<Cow<'a, str>>,
    {
        let table: Column = self.into();
        table.alias(alias)
    }

but not both.

[pimeys]

Also in your example:

        .column(Column::from(("employee", "id")))
        .column(Column::from(("employee", "created")))
        .column(Column::from(("employee", "updated")))
        .column(Column::from(("employee", "deleted")))
        .column(Column::from(("employee", "casino_id")))
        .column(Column::from(("employee", "uuid")))
        .column(Column::from(("employee", "name_first")))
        .column(Column::from(("employee", "name_last")))
        .column(Column::from(("employee", "title")))

can be written (if not aliasing):

    .column(("employee", "id"))
    .column(("employee", "created"))
    .column(("employee", "updated"))
    .column(("employee", "deleted"))
    .column(("employee", "casino_id"))
    .column(("employee", "uuid"))
    .column(("employee", "name_first"))
    .column(("employee", "name_last"))
    .column(("employee", "title"))

[scull7]

I'm running into another issue here. I have a value column which I need to alias. Currently I don't see that it's possible in the released version.

Select::from_table("employee")
	.column(("employee", "id"))
	.column(("employee", "created"))
	// ... etc.
	.value(None.alias("placeholder_column"))

I would like to be able to accomplish something like the above as I have 2 disparate queries which both will resolve to the same object, however, in one query I would like to avoid the sequence of joins necessary to retrieve the placeholder_column. Is this possible?

[pimeys]

Hmm, I'll take a look later today.

[pimeys]

@scull7 There's a PR that should solve your problem here: https://github.com/prisma/quaint/pull/123/files

The type inference here can be tricky, so what you can do with this PR is either:

Select::from_table("employee")
	.column(("employee", "id"))
	.column(("employee", "created"))
	// ... etc.
	.value(val!(Option::<i64>::None).alias("foo"))

or

Select::from_table("employee")
	.column(("employee", "id"))
	.column(("employee", "created"))
	// ... etc.
	.value(val!(Value::Null).alias("foo"))

or if having a variable with a known type

let holder: Option<String> = None;

Select::from_table("employee")
	.column(("employee", "id"))
	.column(("employee", "created"))
	// ... etc.
	.value(val!(holder).alias("placeholder"))

See the tests from the PR and you'll get the idea.

[scull7]

Thank you for adding this. I made a question against the PR: https://github.com/prisma/quaint/pull/123/files#r416723220

Also, when do you expect to have version "0.2" to be released? (working in regulated environments is fun 😉 )

[pimeys]

We have plans to release 0.2 #soon. ;)

What is still missing from it: Microsoft SQL Server support. That should be ready around mid-May.

[pimeys]

P.S. if anybody would like to help, here's the repo! https://github.com/prisma/tiberius/

[scull7]

I would love to, however, I've never actually used MSSQL server.