Ent: Question: Advenced where block

[dom3k]

Hi. One the beginning I want to thank You very much for your work on this project.

I have two tables:

create table if not exists distribution_channels (
    id serial primary key not null, 
    image text not null
);

create table if not exists accounts (
    id serial primary key not null, 
    image text not null,
    distribution_channel_id integer not null references distribution_channels(id)
);

Ent representation:

// Account holds the schema definition for the Account entity.
type Account struct {
    ent.Schema
}

// Fields of the Account.
func (Account) Fields() []ent.Field {
    return []ent.Field{
        field.Text("image").NotEmpty(),
    }
}

// Edges of the Account.
func (Account) Edges() []ent.Edge {
    return []ent.Edge{
        edge.From("distributionChannel", DistributionChannel.Type).
            Ref("accounts").
            Unique(),
    }
}


// DistributionChannel holds the schema definition for the DistributionChannel entity.
type DistributionChannel struct {
    ent.Schema
}

// Fields of the DistributionChannel.
func (DistributionChannel) Fields() []ent.Field {
    return []ent.Field{
        field.Text("image").NotEmpty(),
    }
}

// Edges of the DistributionChannel.
func (DistributionChannel) Edges() []ent.Edge {
    return []ent.Edge{
        edge.To("accounts", Account.Type).StorageKey(edge.Column("distribution_channel_id")),
    }
}

I want to execute query like this:

select 
    accounts.id,
    accounts.image
from accounts 
    inner join distribution_channels on distribution_channels.id = accounts.distribution_channel_id
where 
    accounts.image != distribution_channels.image;

So I do not have any idea how it should looks like in ent.
I do not know how i should define where statement.

    client.Account.Query().
        WithDistributionChannel().
        Where(
            ....
        ).All(ctx)

Can it be done without raw sql query? Thanks.

[a8m]

Hey @dom3k and thanks for the kind words 😃

I'll give it a look later today (or tomorrow), working on documentation at the moment (https://github.com/facebook/ent/pull/843).

[a8m]

Hey @dom3k, there are a few ways to do it, and you can also add it to your generated assets (using custom templates).

I'm pasting here 2 examples how to do it. The 1st version is preferred imo.

func main() {
    // ....

    client.Account.
        Query().
        Where(func(s *sql.Selector) {
            t := sql.Table(distributionchannel.Table).As("dist")
            s.Join(t).On(s.C(account.FieldImage), t.C(distributionchannel.FieldImage))
            s.Where(P(s.C(account.FieldImage), t.C(distributionchannel.FieldImage)))
        }).
        AllX(ctx)

    // Query:
    // SELECT DISTINCT `accounts`.`id`, `accounts`.`image` FROM `accounts` JOIN `distribution_channels` AS `dist` ON `accounts`.`image` = `dist`.`image` WHERE `accounts`.`image` <> `dist`.`image`

    client.Account.
        Query().
        Where(account.HasDistributionChannelWith(func(s *sql.Selector) {
            s.Where(P(sql.Table(account.Table).C(account.FieldImage), s.C(distributionchannel.FieldImage)))
        })).
        AllX(ctx)

    // Query:
    // SELECT DISTINCT `accounts`.`id`, `accounts`.`image` FROM `accounts` JOIN `distribution_channels` AS `dist` ON `accounts`.`image` = `dist`.`image` WHERE `accounts`.`image` <> `dist`.`image`
}

func P(c1, c2 string) *sql.Predicate{
    return sql.P(func(b *sql.Builder) {
        b.Ident(c1).WriteOp(sql.OpNEQ).Ident(c2)
    })
}

Closing, but feel free to continue the discussion if you still need help with this.

[dom3k]

Excellent! It works great. Thanks :)

[sno6]

Hey, @a8m with the above, if I also wanted to fill the edge for the distribution_channel.. so in essence add a select for distribution_channel.* how could that be achieved?

I tried adding a .Select() to the Join, and although it creates a working query, it doesn't seem like it can scan the results through as I get an error:

sql: expected 3 destination arguments in Scan, not 14