Subqueries with Beam

Question

I'm trying to run a subquery on each row of a table. Here is a minimally working example with one table "students".

data StudentT  f
  = StudentT
  { _studentId   :: C f Int
  , _studentName :: C f String
  , _score       :: C f Int
  } deriving Generic

type Student = StudentT Identity
type StudentId = PrimaryKey StudentT Identity

deriving instance Show Student

instance Beamable StudentT
instance Beamable (PrimaryKey StudentT)

instance Table StudentT where
  data PrimaryKey StudentT f = StudentId (Columnar f Int) deriving Generic

data SchoolDb f
  = SchoolDb
  { _students :: f (TableEntity StudentT)
  } deriving Generic

instance Database be SchoolDb

schoolDb :: DatabaseSettings be SchoolDb
schoolDb = defaultDbSettings

What I'm trying to achieve is a query like this:

SELECT s.id,
       s.name,
       s.score,
       (SELECT COUNT(*) FROM students AS t where s.score >= t.score) AS percentile 
FROM students as S

My attempt is the following:

main = do
  conn <- open "test.db"
  runBeamSqliteDebug putStrLn conn $ do
    (students :: [(Student, Int)]) <- runSelectReturningList $ select tablePercentile
    liftIO $ mapM_ print students


tablePercentile :: Q _ _ _ _
tablePercentile = do
  student <- all_ (_students schoolDb)
  let percentile =  subquery_ $ aggregate_ (const countAll_) $ filter_ (\s -> _score s <=. (_score student)) (all_ (_students schoolDb))
  return (student, percentile)

Can someone point me into the right direction?

Edit: Here is the full error message. I figured that subquery_ returns a QGenExpr, so instead of binding it (<-) I put it into a let statement. That simplified the error messages a little.

src/Main.hs:52:71: error:
    • Couldn't match type ‘Database.Beam.Query.Internal.QNested s0’
                     with ‘Database.Beam.Query.QueryInaccessible’
      Expected type: Q SqliteSelectSyntax
                       SchoolDb
                       Database.Beam.Query.QueryInaccessible
                       (StudentT
                          (QExpr
                             Database.Beam.Sqlite.Syntax.SqliteExpressionSyntax
                             (Database.Beam.Query.Internal.QNested s0)),
                        QGenExpr
                          QValueContext
                          (Database.Beam.Backend.SQL.SQL92.Sql92SelectTableExpressionSyntax
                             (Database.Beam.Backend.SQL.SQL92.Sql92SelectSelectTableSyntax
                                SqliteSelectSyntax))
                          s0
                          Int)
        Actual type: Q SqliteSelectSyntax
                       SchoolDb
                       (Database.Beam.Query.Internal.QNested s0)
                       (StudentT
                          (QExpr
                             (Database.Beam.Backend.SQL.SQL92.Sql92SelectTableExpressionSyntax
                                (Database.Beam.Backend.SQL.SQL92.Sql92SelectSelectTableSyntax
                                   SqliteSelectSyntax))
                             (Database.Beam.Query.Internal.QNested s0)),
                        QGenExpr
                          QValueContext
                          (Database.Beam.Backend.SQL.SQL92.Sql92SelectTableExpressionSyntax
                             (Database.Beam.Backend.SQL.SQL92.Sql92SelectSelectTableSyntax
                                SqliteSelectSyntax))
                          s0
                          Int)
    • In the first argument of ‘select’, namely ‘tablePercentile’
      In the second argument of ‘($)’, namely ‘select tablePercentile’
      In a stmt of a 'do' block:
        (students :: [(Student, Int)]) <- runSelectReturningList
                                            $ select tablePercentile
   |
52 |     (students :: [(Student, Int)]) <- runSelectReturningList $ select tablePercentile
   |                                                                       ^^^^^^^^^^^^^^^

Answer 1

This being my first time using Beam, I found it easier to, instead of fixing the code here, start from scratch, using the examples involving aggregates in the user guide as a reference:

tablePercentile =
  aggregate_ (\(student, student') -> (group_ (_studentId student), countAll_))
    . filter_ (\(student, student') -> (_score student <=. _score student'))
    $ (,) <$> all_ (_students schoolDb) <*> all_ (_students schoolDb)

This amounts to an inner join of the table with itself, with filter_ setting up the join condition and aggregate_ handling the grouping and counting. Note that this query only retrieves the student ids, as opposed to the full records. That is due to the usual impossibility of getting more than aggregates and the columns used for grouping from a GROUP BY-using query. One way to deal with that is using a subquery to pass the ids on:

tablePercentile = do
  (sid, cou) <- aggregate_ (\(student, student') -> (group_ (_studentId student), countAll_))
    . filter_ (\(student, student') -> (_score student <=. _score student'))
    $ (,) <$> all_ (_students schoolDb) <*> all_ (_students schoolDb)
  (\student -> (student, cou))
    <$> filter_ (\student -> _studentId student ==. sid) (all_ (_students schoolDb))
-- N.B.: The last line of the do-block might be written as
-- (,) <$> filter_ (\student -> _studentId student ==. sid) (all_ (_students schoolDb)) <*> pure cou

This works as intended:

sqlite> SELECT * from Students;
Id|Name|Score
1|Alice|9
2|Bob|7
3|Carol|6
4|David|8
5|Esther|10
6|Francis|6

GHCi> :main
SELECT "t1"."id" AS "res0", "t1"."name" AS "res1", "t1"."score" AS "res2", "t0"."res1" AS "res3" FROM (SELECT "t0"."id" AS "res0", COUNT(*) AS "res1" FROM "students" AS "t0" INNER JOIN "students" AS "t1" WHERE ("t0"."score")<=("t1"."score") GROUP BY "t0"."id") AS "t0" INNER JOIN "students" AS "t1" WHERE ("t1"."id")=("t0"."res0");
-- With values: []
(StudentT {_studentId = 1, _studentName = "Alice", _score = 9},2)
(StudentT {_studentId = 2, _studentName = "Bob", _score = 7},4)
(StudentT {_studentId = 3, _studentName = "Carol", _score = 6},6)
(StudentT {_studentId = 4, _studentName = "David", _score = 8},3)
(StudentT {_studentId = 5, _studentName = "Esther", _score = 10},1)
(StudentT {_studentId = 6, _studentName = "Francis", _score = 6},6)

On a closing note, the error in your code appears, as far as I understand it, to have to do with attempting to compare incomparable things in the (<=.) condition. Your original code (using a monadic bind for percentile) compiles if the filter_ is commented out. It may have something to do with the GROUP BY issue I mentioned, though I'm not sure about it.