use serde_json::{
json,
Value,
};
use sqlxo::{
blocks::SelectType,
web::{
WebDeleteFilter,
WebReadFilter,
WebUpdateFilter,
},
Buildable,
QueryBuilder,
ReadQueryPlan,
};
use uuid::Uuid;
use crate::helpers::{
AppUser,
AppUserDto,
HardDeleteItem,
HardDeleteItemDto,
Item,
ItemDto,
ItemSelectiveJoinDto,
NormalizeString,
UpdateItem,
UpdateItemDto,
UpdateItemUpdate,
};
#[test]
fn deserialize_itemdto_sqlxo_json() {
let json: Value = json!({
"filter": {
"and": [
{ "differentName": { "like": "%Sternlampe%" } },
{ "or": [
{ "price": { "gt": 18.00 } },
{ "description": { "neq": "von Hohlweg" } }
]}
]
},
"sort": [
{ "differentName": "asc" },
{ "description": "desc" }
],
"pageSize": 10,
"pageNo": 1
});
let f: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
assert_eq!(f.page_size, Some(10));
assert_eq!(f.page_no, Some(1));
}
#[test]
fn query_builder_from_web_query_filter() {
let json: Value = json!({
"filter": {
"and": [
{ "differentName": { "like": "%Sternlampe%" } },
{ "or": [
{ "price": { "gt": 18.00 } },
{ "description": { "neq": "von Hohlweg" } }
]}
]
},
"sort": [
{ "differentName": "asc" },
{ "description": "desc" }
],
"pageSize": 10,
"pageNo": 1
});
let f: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
let plan: ReadQueryPlan<Item> =
QueryBuilder::<Item>::from_web_read::<ItemDto>(&f).build();
let sql = plan.sql(SelectType::Star).trim_start().normalize();
assert!(
sql.contains(r#"LEFT JOIN material AS "material__""#),
"expected auto-loaded material join in SQL: {sql}"
);
assert!(
sql.contains(r#"LEFT JOIN supplier AS "material__supplier__""#),
"expected auto-loaded nested supplier join in SQL: {sql}"
);
assert!(
sql.contains(r#"LEFT JOIN item_tag AS "tags__pivot__""#),
"expected auto-loaded lazy many-to-many join in SQL: {sql}"
);
assert!(
sql.contains(r#""item"."name" LIKE $1"#),
"expected filter clause in SQL: {sql}"
);
assert!(
sql.contains(
r#"ORDER BY "item"."name" ASC, "item"."description" DESC"#
),
"expected sort clause in SQL: {sql}"
);
}
#[test]
fn web_payload_search_auto_includes_full_tree_joins() {
let json: Value = json!({
"filter": {
"differentName": { "like": "%Sternlampe%" }
},
"search": "bolt",
"sort": [
{ "differentName": "asc" }
]
});
let f: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
let plan: ReadQueryPlan<Item> =
QueryBuilder::<Item>::from_web_read::<ItemDto>(&f).build();
let sql = plan.sql(SelectType::Star).trim_start().normalize();
assert!(
sql.contains(r#"LEFT JOIN material AS "material__""#),
"expected auto-loaded join in SQL: {sql}"
);
assert!(
sql.contains(r#""item"."name" LIKE $1"#),
"expected filter clause in SQL: {sql}"
);
assert!(
sql.contains("CASE WHEN") && sql.contains("to_tsquery('simple'"),
"expected majority-gated token search predicate in SQL: {sql}"
);
assert!(
!sql.contains("WORD_SIMILARITY("),
"web search should disable fuzzy similarity by default: {sql}"
);
assert!(
sql.contains(r#""material__"."name"::text"#),
"expected joined fields in search predicate: {sql}"
);
assert!(
sql.contains(r#"ORDER BY "item"."name" ASC"#),
"expected user sort order to be preserved: {sql}"
);
}
#[test]
fn web_search_payload_is_plain_string() {
let json: Value = json!({
"search": { "query": "bolt" }
});
let result = serde_json::from_value::<WebReadFilter<ItemDto>>(json);
assert!(
result.is_err(),
"expected object search payload to be rejected"
);
}
#[test]
fn web_query_rejects_nested_page_object() {
let json: Value = json!({
"page": { "pageNo": 1, "pageSize": 10 }
});
let result = serde_json::from_value::<WebReadFilter<ItemDto>>(json);
assert!(
result.is_err(),
"expected nested page object to be rejected"
);
}
#[test]
fn web_sort_rejects_multiple_fields_per_entry() {
let json: Value = json!({
"sort": [
{
"differentName": "asc",
"description": "desc"
}
],
});
let result = serde_json::from_value::<WebReadFilter<ItemDto>>(json);
assert!(
result.is_err(),
"expected sort entries with multiple fields to be rejected"
);
}
#[test]
fn web_query_auto_loads_nested_join_paths_by_default() {
let json: Value = json!({});
let f: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
let sql = QueryBuilder::<Item>::from_web_read::<ItemDto>(&f)
.build()
.sql(SelectType::Star)
.trim_start()
.normalize();
assert!(
sql.contains(r#"LEFT JOIN material AS "material__""#),
"expected material join in SQL: {sql}"
);
assert!(
sql.contains(r#"LEFT JOIN supplier AS "material__supplier__""#),
"expected nested supplier join in SQL: {sql}"
);
}
#[test]
fn web_query_uses_declared_default_join_paths() {
let json: Value = json!({});
let f: WebReadFilter<ItemSelectiveJoinDto> =
serde_json::from_value(json).expect("valid ItemSelectiveJoinDto");
let sql = QueryBuilder::<Item>::from_web_read::<ItemSelectiveJoinDto>(&f)
.build()
.sql(SelectType::Star)
.trim_start()
.normalize();
assert!(
sql.contains(r#"LEFT JOIN material AS "material__""#),
"expected configured material join in SQL: {sql}"
);
assert!(
sql.contains(r#"LEFT JOIN supplier AS "material__supplier__""#),
"expected configured nested supplier join in SQL: {sql}"
);
assert!(
!sql.contains(r#"LEFT JOIN item_tag AS "tags__pivot__""#),
"unexpected lazy many-to-many join should not be auto-loaded: {sql}"
);
}
#[test]
fn web_query_plain_builder_uses_base_scope_without_auto_relations() {
let json: Value = json!({
"filter": {
"differentName": { "like": "%Sternlampe%" }
},
"sort": [
{ "differentName": "asc" }
],
"pageSize": 10,
"pageNo": 1
});
let filter: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
let sql = QueryBuilder::<Item>::from_web_read_plain::<ItemDto>(&filter)
.build()
.sql(SelectType::Star)
.trim_start()
.normalize();
assert!(
!sql.contains("LEFT JOIN"),
"plain web query should not emit SQL joins: {sql}"
);
assert!(
sql.contains(r#""item"."name" LIKE $1"#),
"expected filter clause in SQL: {sql}"
);
assert!(
sql.contains(r#"ORDER BY "item"."name" ASC"#),
"expected user sort order to be preserved: {sql}"
);
}
#[test]
fn web_query_into_update_builds_sql() {
let test_id = Uuid::new_v4();
let json: Value = json!({
"filter": { "id": { "eq": test_id } }
});
let filter: WebUpdateFilter<UpdateItemDto> =
serde_json::from_value(json).expect("valid UpdateItemDto filter");
let update = UpdateItemUpdate {
name: Some("updated".into()),
..Default::default()
};
let plan =
QueryBuilder::<UpdateItem>::from_web_update::<UpdateItemDto>(&filter)
.model(update)
.build();
assert_eq!(
plan.sql().normalize(),
"UPDATE update_item SET updated_at = NOW(), name = COALESCE($1, \
name), description = COALESCE($2, description), price = COALESCE($3, \
price) WHERE \"update_item\".\"id\" = $4"
);
}
#[test]
fn web_query_into_delete_builds_sql() {
let json: Value = json!({
"filter": { "name": { "eq": "obsolete" } }
});
let filter: WebDeleteFilter<HardDeleteItemDto> =
serde_json::from_value(json).expect("valid HardDeleteItemDto filter");
let plan = QueryBuilder::<HardDeleteItem>::from_web_delete::<
HardDeleteItemDto,
>(&filter)
.build();
assert_eq!(
plan.sql().normalize(),
"DELETE FROM hard_delete_item WHERE \"hard_delete_item\".\"name\" = $1"
);
}
#[test]
fn web_query_update_rejects_having() {
let json: Value = json!({
"having": { "count": { "gt": 1 } }
});
let result = serde_json::from_value::<WebUpdateFilter<UpdateItemDto>>(json);
assert!(result.is_err());
}
#[test]
fn web_query_read_rejects_having() {
let json: Value = json!({
"having": { "count": { "gt": 1 } }
});
let result = serde_json::from_value::<WebReadFilter<ItemDto>>(json);
assert!(result.is_err());
}
#[test]
fn web_query_search_requires_full_text_support() {
let json: Value = json!({
"search": "bolt"
});
let filter: WebReadFilter<HardDeleteItemDto> =
serde_json::from_value(json).expect("valid HardDeleteItemDto filter");
let result = QueryBuilder::<HardDeleteItem>::try_from_web_read::<
HardDeleteItemDto,
>(&filter);
let err = match result {
Ok(_) => panic!("expected search to be unsupported for HardDeleteItem"),
Err(err) => err,
};
assert!(
matches!(
err,
sqlxo::SqlxoError::WebQuery(
sqlxo::WebQueryError::SearchUnsupported { .. }
)
),
"expected SearchUnsupported error but got {err:?}"
);
}
#[test]
fn web_query_search_ignores_non_searchable_auto_joins() {
let json: Value = json!({
"search": "alice"
});
let filter: WebReadFilter<AppUserDto> =
serde_json::from_value(json).expect("valid AppUserDto filter");
let plan: ReadQueryPlan<AppUser> =
QueryBuilder::<AppUser>::from_web_read::<AppUserDto>(&filter).build();
let sql = plan.sql(SelectType::Star).trim_start().normalize();
assert!(
sql.contains(r#"LEFT JOIN profile AS "profile__""#),
"expected requested profile join to be present: {sql}"
);
assert!(
sql.contains("CASE WHEN") && sql.contains("websearch_to_tsquery"),
"expected search predicate to be generated: {sql}"
);
assert!(
!sql.contains(r#""profile__"."bio"::text"#),
"non-searchable joins must be ignored for search document building: \
{sql}"
);
}
#[test]
fn web_query_plain_search_stays_base_scope() {
let json: Value = json!({
"search": "bolt"
});
let filter: WebReadFilter<ItemDto> =
serde_json::from_value(json).expect("valid ItemDtoFilter");
let plan: ReadQueryPlan<Item> =
QueryBuilder::<Item>::from_web_read_plain::<ItemDto>(&filter).build();
let sql = plan.sql(SelectType::Star).trim_start().normalize();
assert!(
!sql.contains("LEFT JOIN"),
"plain web search should not emit SQL joins: {sql}"
);
assert!(
sql.contains("CASE WHEN") && sql.contains("to_tsquery('simple'"),
"expected token-based search predicate to be generated: {sql}"
);
assert!(
!sql.contains("WORD_SIMILARITY("),
"plain web search should disable fuzzy similarity by default: {sql}"
);
assert!(
!sql.contains(r#""material__"."name"::text"#),
"plain web search should not include relation scoped fields: {sql}"
);
}