DeleteAll() errors on a many-to-many relationship

[menma1234]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.13.0

What is your database and version (eg. Postgresql 10)

Postgres 10

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

N/A

If this happened at runtime what code produced the issue? (if not applicable leave blank)

package main

import (
        "context"
        "database/sql"
        "fmt"

        _ "github.com/jackc/pgx/v4/stdlib"
        "github.com/volatiletech/null/v8"
        "github.com/volatiletech/sqlboiler/v4/boil"

        "github.com/test/models"
)

func main() {
        ctx := context.Background()
        db, err := sql.Open("pgx", "postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable&statement_cache_mode=describe")
        if err != nil {
                fmt.Println(err)
                return
        }

        first := &models.First{SomeColumn: null.StringFrom("asdf")}
        if err := first.Insert(ctx, db, boil.Infer()); err != nil {
                fmt.Println(err)
                return
        }

        boil.DebugMode = true
        if _, err := first.Seconds().DeleteAll(ctx, db); err != nil {
                fmt.Println(err)
                return
        }
}

Output:

DELETE FROM "second" WHERE ("first_to_second"."first_id"=$1);
[2]
models: unable to delete all from second: ERROR: missing FROM-clause entry for table "first_to_second" (SQLSTATE 42P01)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

N/A

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

START TRANSACTION;

CREATE TABLE first
(
    id serial PRIMARY KEY,
    some_column TEXT
);

CREATE TABLE second
(
    id serial PRIMARY KEY,
    some_column TEXT
);

CREATE TABLE first_to_second
(
    first_id int NOT NULL,
    second_id int NOT NULL,
    PRIMARY KEY (first_id, second_id),
    FOREIGN KEY (first_id) REFERENCES first(id),
    FOREIGN KEY (second_id) REFERENCES second(id)
);

COMMIT;

Further information. What did you do, what did you expect?

With a many-to-many relationship, we wanted to remove all of the relationships between the two tables (i.e. remove all entries from the bridge table where the ID matches the first type's ID). Instinctively, I tried using first.Seconds().DeleteAll() but this appears to try to delete all records from the second table though the WHERE clause is wrong and gives a WHERE clause containing the bridge table's name, causing a SQL error.

Our workaround is currently to call first.SetSeconds() with an empty list, but it feels like something should be done about the first attempted usage (whether it's removing DeleteAll from this scenario or fixing it to do the right thing) since it generates invalid SQL.

[stephenafamo]

At the moment, SQLBoiler's query building can be used to generate invalid queries. There is nothing stopping a user from using qm.InnerJoin() in a DELETE query which is technically wrong.

There are some ideas around overhauling the query system, but if it introduces a breaking change, then it will have to happen with the next major version.

If you have any ideas and can send in a PR, I'll be happy to review.