sea-query
sea-query copied to clipboard
SeaQL
Add SELECT EXISTS, Fix #118
This would add the SELECT EXISTS statement with this api:
Query::select()
.exists()
.from(Char::Table)
.and_where(Expr::col(Char::Character).eq("A"))
I had a doubt about the api to use, my second shot would have been this one, which would have been syntactically more equivalent to the final SQL query but more verbose (let me know if you'd preferred this one):
Query::exists(
Query::select()
.from(Char::Table)
.and_where(Expr::col(Char::Character).eq("A")),
Alias::new("exists")
)
I added some comments into the code to describe the process I used since it's relatively "desynchronised" with the rest of the prepare_select_statement due to one thing:
- The
SELECT EXISTSstatement needs to be executed "as it self", without any table to query in the main expression.
Due to this I couldn't use a function (because if no table is passed, it only results to a SELECT string without any further expression).
@maintainers An additional note on this one: if both exists unary operator and wildcard issues are fixed (I'll submit the PR for the wildcard and the draft-PR for exists unary operator (can't finish because an easy way would be to rely on wildcard) in few hours), we could refactor this one or even just not merge this at all because it would be now possible to do something like:
Query::select()
.expr_as(
Expr::exists(
Query::select()
.wildcard()
.from(Char::Table)
),
Alias::new('exists');
)
Which is not so ugly imo, it should results to something like we wanted:
SELECT EXISTS(SELECT * FROM `character`) as `exists`
Plus! We could now chain multi-select-exists it more elegantly:
Query::select()
.expr_as(
Expr::exists(
Query::select()
.wildcard()
.from(Char::Table)
),
Alias::new('character_exists');
)
.expr_as(
Expr::exists(
Query::select()
.wildcard()
.from(Glyph::Table)
),
Alias::new('glyph_exists');
)
SELECT EXISTS(SELECT * FROM `character`) as `character_exists`,
EXISTS(SELECT * FROM `glyph`) as `glyph_exists`
Query::select() .expr_as( Expr::exists( Query::select() .wildcard() .from(Char::Table) ), Alias::new('exists'); )
This looks elegant!
The two related PRs are ready for reviews, I made the .wildcard() call unnecessary to call in the exists SelectStatement, it will be automatically injected.
Feel free to not merge this one if both of the other two get merged.
Oh and if we place the test cases as doc tests, we can save some effort by not having to put the test in 3 different places, and the docs look better too.
