381 lines
15 KiB
Haskell
381 lines
15 KiB
Haskell
module Feature.Query.RelatedQueriesSpec where
|
|
|
|
import Network.Wai (Application)
|
|
|
|
import Network.HTTP.Types
|
|
import Test.Hspec
|
|
import Test.Hspec.Wai
|
|
import Test.Hspec.Wai.JSON
|
|
|
|
import Protolude hiding (get)
|
|
import SpecHelper
|
|
|
|
spec :: SpecWith ((), Application)
|
|
spec = describe "related queries" $ do
|
|
context "related orders" $ do
|
|
it "works on a many-to-one relationship" $ do
|
|
get "/projects?select=id,clients(name)&order=clients(name).nullsfirst" `shouldRespondWith`
|
|
[json|[
|
|
{"id":5,"clients":null},
|
|
{"id":3,"clients":{"name":"Apple"}},
|
|
{"id":4,"clients":{"name":"Apple"}},
|
|
{"id":1,"clients":{"name":"Microsoft"}},
|
|
{"id":2,"clients":{"name":"Microsoft"}} ]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/projects?select=id,client:clients(name)&order=client(name).asc" `shouldRespondWith`
|
|
[json|[
|
|
{"id":3,"client":{"name":"Apple"}},
|
|
{"id":4,"client":{"name":"Apple"}},
|
|
{"id":1,"client":{"name":"Microsoft"}},
|
|
{"id":2,"client":{"name":"Microsoft"}},
|
|
{"id":5,"client":null} ]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/videogames?select=id,computed_designers(id)&order=computed_designers(id).desc" `shouldRespondWith`
|
|
[json|[
|
|
{"id":3,"computed_designers":{"id":2}},
|
|
{"id":4,"computed_designers":{"id":2}},
|
|
{"id":1,"computed_designers":{"id":1}},
|
|
{"id":2,"computed_designers":{"id":1}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works on a one-to-one relationship and jsonb column" $ do
|
|
get "/trash?select=id,trash_details(id,jsonb_col)&order=trash_details(jsonb_col->key).asc" `shouldRespondWith`
|
|
[json|[
|
|
{"id":2,"trash_details":{"id":2,"jsonb_col":{"key": 6}}},
|
|
{"id":3,"trash_details":{"id":3,"jsonb_col":{"key": 8}}},
|
|
{"id":1,"trash_details":{"id":1,"jsonb_col":{"key": 10}}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/trash?select=id,trash_details(id,jsonb_col)&order=trash_details(jsonb_col->key).desc" `shouldRespondWith`
|
|
[json|[
|
|
{"id":1,"trash_details":{"id":1,"jsonb_col":{"key": 10}}},
|
|
{"id":3,"trash_details":{"id":3,"jsonb_col":{"key": 8}}},
|
|
{"id":2,"trash_details":{"id":2,"jsonb_col":{"key": 6}}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works on an embedded resource" $ do
|
|
get "/users?select=name,tasks(id,name,projects(id,name))&tasks.order=projects(id).desc&limit=1" `shouldRespondWith`
|
|
[json| [{
|
|
"name":"Angela Martin",
|
|
"tasks":[
|
|
{"id": 3, "name":"Design w10","projects":{"id":2,"name":"Windows 10"}},
|
|
{"id": 4, "name":"Code w10","projects":{"id":2,"name":"Windows 10"}},
|
|
{"id": 1, "name":"Design w7","projects":{"id":1,"name":"Windows 7"}},
|
|
{"id": 2, "name":"Code w7","projects":{"id":1,"name":"Windows 7"}}
|
|
]
|
|
}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/users?select=name,tasks(id,name,projects(id,name))&tasks.order=projects(id).desc,name&limit=1" `shouldRespondWith`
|
|
[json| [{
|
|
"name":"Angela Martin",
|
|
"tasks":[
|
|
{"id": 4, "name":"Code w10","projects":{"id":2,"name":"Windows 10"}},
|
|
{"id": 3, "name":"Design w10","projects":{"id":2,"name":"Windows 10"}},
|
|
{"id": 2, "name":"Code w7","projects":{"id":1,"name":"Windows 7"}},
|
|
{"id": 1, "name":"Design w7","projects":{"id":1,"name":"Windows 7"}}
|
|
]
|
|
}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/users?select=name,tasks(id,name,projects(id,name))&tasks.order=projects(id).asc&limit=1" `shouldRespondWith`
|
|
[json|[{
|
|
"name":"Angela Martin",
|
|
"tasks":[
|
|
{"id":1,"name":"Design w7","projects":{"id":1,"name":"Windows 7"}},
|
|
{"id":2,"name":"Code w7","projects":{"id":1,"name":"Windows 7"}},
|
|
{"id":3,"name":"Design w10","projects":{"id":2,"name":"Windows 10"}},
|
|
{"id":4,"name":"Code w10","projects":{"id":2,"name":"Windows 10"}}
|
|
]
|
|
}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "fails when is not a to-one relationship" $ do
|
|
get "/clients?select=*,projects(*)&order=projects(id)" `shouldRespondWith`
|
|
[json|{
|
|
"code":"PGRST118",
|
|
"details":"'clients' and 'projects' do not form a many-to-one or one-to-one relationship",
|
|
"hint":null,
|
|
"message":"A related order on 'projects' is not possible"
|
|
}|]
|
|
{ matchStatus = 400
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/clients?select=*,pros:projects(*)&order=pros(id)" `shouldRespondWith`
|
|
[json|{
|
|
"code":"PGRST118",
|
|
"details":"'clients' and 'pros' do not form a many-to-one or one-to-one relationship",
|
|
"hint":null,
|
|
"message":"A related order on 'pros' is not possible"
|
|
}|]
|
|
{ matchStatus = 400
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/designers?select=id,computed_videogames(id)&order=computed_videogames(id).desc" `shouldRespondWith`
|
|
[json|{
|
|
"code":"PGRST118",
|
|
"details":"'designers' and 'computed_videogames' do not form a many-to-one or one-to-one relationship",
|
|
"hint":null,
|
|
"message":"A related order on 'computed_videogames' is not possible"
|
|
}|]
|
|
{ matchStatus = 400
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "fails when the resource is not embedded" $
|
|
get "/projects?select=id,clients(name)&order=clientsx(name).nullsfirst" `shouldRespondWith`
|
|
[json|{
|
|
"code":"PGRST108",
|
|
"details":null,
|
|
"hint":"Verify that 'clientsx' is included in the 'select' query parameter.",
|
|
"message":"'clientsx' is not an embedded resource in this request"
|
|
}|]
|
|
{ matchStatus = 400
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
context "related conditions through null operator on embed" $ do
|
|
it "works on a many-to-one relationship" $ do
|
|
get "/projects?select=name,clients()&clients=not.is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"Windows 7"},
|
|
{"name":"Windows 10"},
|
|
{"name":"IOS"},
|
|
{"name":"OSX"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/projects?select=name,clients()&clients=is.null" `shouldRespondWith`
|
|
[json|[{"name":"Orphan"}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/projects?select=name,computed_clients()&computed_clients=is.null" `shouldRespondWith`
|
|
[json|[{"name":"Orphan"}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works on a one-to-many relationship" $ do
|
|
get "/entities?select=name,child_entities()&child_entities=not.is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"entity 1"},
|
|
{"name":"entity 2"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/entities?select=name,child_entities()&child_entities=is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"entity 3"},
|
|
{"name":null}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/entities?select=name,childs:child_entities()&childs=is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"entity 3"},
|
|
{"name":null}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works on a many-to-many relationship" $ do
|
|
get "/users?select=name,tasks()&tasks.id=eq.1&tasks=not.is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"Angela Martin"},
|
|
{"name":"Dwight Schrute"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/users?select=name,tasks()&tasks.id=eq.1&tasks=is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"Michael Scott"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works on nested embeds" $ do
|
|
get "/entities?select=name,child_entities(name,grandchild_entities())&child_entities.grandchild_entities=not.is.null&child_entities=not.is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"entity 1","child_entities":[{"name":"child entity 1"}, {"name":"child entity 2"}]}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "can do an or across embeds" $
|
|
get "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)" `shouldRespondWith`
|
|
[json|[
|
|
{"id":1,"name":"Walmart"},
|
|
{"id":2,"name":"Target"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "doesn't interfere filtering when embedding using the column name" $
|
|
get "/projects?select=name,client_id,client:client_id(name)&client_id=eq.2" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"IOS","client_id":2,"client":{"name":"Apple"}},
|
|
{"name":"OSX","client_id":2,"client":{"name":"Apple"}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "doesn't interfere filtering on column names used for disambiguation" $
|
|
get "/user_friend?select=*,user1(*)&user1=eq.a02fb934-3a4d-469b-a6b6-4fcd88b973cf" `shouldRespondWith`
|
|
[json|[]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "doesn't interfere filtering on column names that are the same as the relation name" $
|
|
get "/tournaments?select=*,status(*)&status=eq.3" `shouldRespondWith`
|
|
[json|[]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
-- "?table=not.is.null" does a "table IS DISTINCT FROM NULL" instead of a "table IS NOT NULL"
|
|
-- https://github.com/PostgREST/postgrest/issues/2800#issuecomment-1720315818
|
|
it "embeds verifying that the entire target table row is not null" $ do
|
|
get "/table_b?select=name,table_a(name)&table_a=not.is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"Test 1","table_a":{"name":"Not null 1"}},
|
|
{"name":"Test 2","table_a":{"name":null}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
get "/table_b?select=name,table_a()&table_a=is.null" `shouldRespondWith`
|
|
[json|[
|
|
{"name":"Test 3"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [matchContentTypeJson]
|
|
}
|
|
|
|
it "works with count=exact" $ do
|
|
request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
|
|
[("Prefer", "count=exact")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"name":"Windows 7", "clients":{"name":"Microsoft"}},
|
|
{"name":"Windows 10", "clients":{"name":"Microsoft"}},
|
|
{"name":"IOS", "clients":{"name":"Apple"}},
|
|
{"name":"OSX", "clients":{"name":"Apple"}}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-3/4" ]
|
|
}
|
|
request methodGet "/projects?select=name,clients()&clients=is.null"
|
|
[("Prefer", "count=exact")] ""
|
|
`shouldRespondWith`
|
|
[json|[{"name":"Orphan"}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-0/1" ]
|
|
}
|
|
request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
|
|
[("Prefer", "count=exact")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"id":1,"name":"Walmart"},
|
|
{"id":2,"name":"Target"}
|
|
]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-1/2" ]
|
|
}
|
|
|
|
it "works with count=planned" $ do
|
|
request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
|
|
[("Prefer", "count=planned")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"name":"Windows 7", "clients":{"name":"Microsoft"}},
|
|
{"name":"Windows 10", "clients":{"name":"Microsoft"}},
|
|
{"name":"IOS", "clients":{"name":"Apple"}},
|
|
{"name":"OSX", "clients":{"name":"Apple"}}
|
|
]|]
|
|
{ matchStatus = 206
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-3/1200" ]
|
|
}
|
|
request methodGet "/projects?select=name,clients()&clients=is.null"
|
|
[("Prefer", "count=planned")] ""
|
|
`shouldRespondWith`
|
|
[json|[{"name":"Orphan"}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-0/1" ]
|
|
}
|
|
request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
|
|
[("Prefer", "count=planned")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"id":1,"name":"Walmart"},
|
|
{"id":2,"name":"Target"}
|
|
]|]
|
|
{ matchStatus = 206
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-1/952" ]
|
|
}
|
|
|
|
it "works with count=estimated" $ do
|
|
request methodGet "/projects?select=name,clients(name)&clients=not.is.null"
|
|
[("Prefer", "count=estimated")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"name":"Windows 7", "clients":{"name":"Microsoft"}},
|
|
{"name":"Windows 10", "clients":{"name":"Microsoft"}},
|
|
{"name":"IOS", "clients":{"name":"Apple"}},
|
|
{"name":"OSX", "clients":{"name":"Apple"}}
|
|
]|]
|
|
{ matchStatus = 206
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-3/1200" ]
|
|
}
|
|
request methodGet "/projects?select=name,clients()&clients=is.null"
|
|
[("Prefer", "count=estimated")] ""
|
|
`shouldRespondWith`
|
|
[json|[{"name":"Orphan"}]|]
|
|
{ matchStatus = 200
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-0/1" ]
|
|
}
|
|
request methodGet "/client?select=*,clientinfo(),contact()&clientinfo.other=ilike.*main*&contact.name=ilike.*tabby*&or=(clientinfo.not.is.null,contact.not.is.null)"
|
|
[("Prefer", "count=estimated")] ""
|
|
`shouldRespondWith`
|
|
[json|[
|
|
{"id":1,"name":"Walmart"},
|
|
{"id":2,"name":"Target"}
|
|
]|]
|
|
{ matchStatus = 206
|
|
, matchHeaders = [ matchContentTypeJson
|
|
, "Content-Range" <:> "0-1/952" ]
|
|
}
|