Comments (6)
Have you been using these as extensions methods in your own project yet?
I'm curious to know if using where
+ whereIf true
side-by-side in the same query works properly, specifically regarding the parentheses / nesting of the where statements. I'm sure that people would try to use them together, so it would need to handle the parentheses properly in that case.
Can you print out an example of the generated SQL query in the case?
from sqlhydra.
When using both where and whereIf in the same query, they're composed with AND. I added these two tests locally and they both passed:
"test "whereIf true" {
let query =
select {
for c in main.Customer do
where (c.FirstName = "John")
whereIf (true) (c.LastName = "Doe")
}
let sql = query.ToKataQuery() |> toSql
Expect.equal
sql
"SELECT * FROM "main"."Customer" AS "c" WHERE ("c"."FirstName" = @p0) AND ("c"."LastName" = @p1) "
""}"
`test "whereIf false" {
let query =
select {
for c in main.Customer do
where (c.FirstName = "John")
whereIf (false) (c.LastName = "Doe")
}
let sql = query.ToKataQuery() |> toSql
Expect.equal
sql
"SELECT * FROM \"main\".\"Customer\" AS \"c\" WHERE (\"c\".\"FirstName\" = @p0)"
""}`
[11:35:22 DBG] Sqlite.Query Unit Tests.whereIf true starting... <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf true passed in 00:00:00. <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf false starting... <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf false passed in 00:00:00. <Expecto>
from sqlhydra.
The reason I am hesitant to add these methods to the builder is that I would like to avoid an explosion of subtle variations of where___
and orderBy___
methods, especially considering that users can opt to add these kinds of methods to the builder as extensions if they really need them.
First we add whereIf
which generates an (<condition1>) AND (<condition2>)
, and then someone asks for an OR
variation.
So I think that it would be better to create one extra where
that would have all the configuration options.
Maybe something like this:
select {
for c in main.Customer do
where (filters.LastName.IsSome) (Where.And) (c.LastName = filters.LastName.Value)
where (filters.FirstName.IsSome) (Where.And) (c.FirstName = filters.FirstName.Value)
Which admittedly is kind of ugly.
from sqlhydra.
Here is another possible variation of the conditional where clause that looks pretty clean:
select {
for c in main.Customer do
where (
(filters.LastName.IsSome && c.LastName = filters.LastName.Value) &&
(filters.FirstName.IsSome && c.FirstName = filters.FirstName.Value)
)
}
from sqlhydra.
My apologies for dragging my feet on this for so long.
While I'm mulling this design idea around, I am adding a new feature in the meantime that provides direct access to the underlying SqlKata.Query
object via the kata
operation.
This will make it pretty easy to add conditional where and order by clauses, as well as many other query customizations.
https://github.com/JordanMarr/SqlHydra/releases/tag/query-v2.0.2
from sqlhydra.
No reason to apologize- I've been the one that's been too busy to respond. Do whatever you feel is best! Thanks for the continuous work you put into it.
from sqlhydra.
Related Issues (20)
- Make a selected column from a leftJoined table option with `Some`
- Support net8.0 HOT 17
- Add documentation and setup configuration on how to clone the repo, build the project, and run tests. HOT 2
- Creating sqlhydra file updates the wrong .fsproj file HOT 9
- SQL Server Temporal Tables Select Issue HOT 8
- Retrieve Multiple result sets (UNION ALL) using SqlHydra.Query HOT 1
- Insert and Update Builders break when column name is the same as another table. HOT 2
- Mapping object with list of objects 1 to n relationship HOT 4
- Best practice for multiple environments and the config.toml file HOT 7
- Design patterns for DDD HOT 8
- Is it possible to support materialized views from Postgres? HOT 22
- Generate types in GitHub workflow generates no table types HOT 5
- Show filter info in SqlHydra.Cli command line output HOT 1
- Custom pooling with SqlHydra HOT 8
- SqlHydra.Cli doesn't map Sqlite longvarchar column types HOT 5
- Error after upgrading V2.5.0: 'Microsoft.FSharp.Core.FSharpOption`1 is not a F# record type. HOT 9
- Public Schema Not Properly Escaped for NpgSQL in v2.4+ HOT 2
- Query with `leftJoin` breaks in v2.5.0 HOT 3
- Is it possible call `date_trunc` from PostgreSQL? HOT 7
- NoWarn 3391 HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlhydra.