Define a QueryBuilder by filtering then sorting

Question

Filtering

The following code in a controller action:

    students <- case searchString' of
        Nothing -> query @Student |> fetch
        (Just str) -> query @Student 
            |> queryOr
                (filterWhereILike (#lastName, "%" <> str <> "%"))
                (filterWhereILike (#firstMidName, "%" <> str <> "%"))
            |> fetch

does:

If searchString' is available, return students whose lastName or firstMidName match searchString'
Otherwise, return all the students

This works great!

Sorting

After filtering, I'd like to sort according to another Maybe Text. I'd like to do something like this:

Setup the queryBuilder with the filtering step done:

        let queryBuilder = case searchString' of
                            Nothing -> query @Student
                            (Just str) -> query @Student 
                                |> queryOr
                                    (filterWhereILike (#lastName, "%" <> str <> "%"))
                                    (filterWhereILike (#firstMidName, "%" <> str <> "%"))

Then do the ordering:

        students <- case sortOrder of
                        (Just "NameAsc") -> queryBuilder |> orderByAsc  #lastName |> fetch
                        (Just "NameDsc") -> queryBuilder |> orderByDesc #lastName |> fetch
                        (Just "DateAsc") -> queryBuilder |> orderByAsc  #enrollmentDate |> fetch
                        (Just "DateDsc") -> queryBuilder |> orderByDesc #enrollmentDate |> fetch
                        Nothing -> queryBuilder |> orderByAsc #lastName |> fetch
                        _ -> queryBuilder |> orderByAsc #lastName |> fetch

However, that (understandably) results in the following:

• Couldn't match expected type ‘QueryBuilder "students"’
              with actual type ‘NoJoinQueryBuilderWrapper "students"’
• In the expression:
    query @Student
      |>
        queryOr
          (filterWhereILike (#lastName, "%" <> str <> "%"))
          (filterWhereILike (#firstMidName, "%" <> str <> "%"))
  In a case alternative:
      (Just str)
        -> query @Student
             |>
               queryOr
                 (filterWhereILike (#lastName, "%" <> str <> "%"))
                 (filterWhereILike (#firstMidName, "%" <> str <> "%"))
  In the expression:
    case searchString' of
      Nothing -> query @Student
      (Just str)
        -> query @Student
             |>
               queryOr
                 (filterWhereILike (#lastName, "%" <> str <> "%"))
                 (filterWhereILike (#firstMidName, "%" <> str <> "%"))typecheck

Question

Any suggestions for how to set this up?

The original code

If it's of any help, this is more or less a conversion of the following from C#

            var students = _context.Students.Select(student => student);
                        
            if (!String.IsNullOrEmpty(searchString)) 
                students = students.Where(student => 
                    student.LastName.Contains(searchString) || 
                    student.FirstMidName.Contains(searchString));

            if      (sortOrder == SortOrder.NameAsc) students = students.OrderBy(          student => student.LastName);
            else if (sortOrder == SortOrder.NameDsc) students = students.OrderByDescending(student => student.LastName);
            else if (sortOrder == SortOrder.DateAsc) students = students.OrderBy(          student => student.EnrollmentDate);
            else if (sortOrder == SortOrder.DateDsc) students = students.OrderByDescending(student => student.EnrollmentDate);
            else                                     students = students.OrderBy(          student => student.LastName);

References

The QueryBuilder section of the IHP manual:

https://ihp.digitallyinduced.com/Guide/querybuilder.html

Unless I missed something, there doesn't seem to be an example similar to the above.

There is another section for Raw SQL Queries as well.

Update 1

Here's one approach which works:

students <- case searchString' of

    Nothing -> query @Student 
        |> (case sortOrder of
                (Just "NameAsc") -> orderByAsc #lastName
                (Just "NameDsc") -> orderByDesc #lastName
                (Just "DateAsc") -> orderByAsc  #enrollmentDate
                (Just "DateDsc") -> orderByDesc #enrollmentDate
                Nothing -> orderByAsc #lastName
                _ -> orderByAsc #lastName)
        |> fetch

    (Just str) -> query @Student 
        |> queryOr
            (filterWhereILike (#lastName, "%" <> str <> "%"))
            (filterWhereILike (#firstMidName, "%" <> str <> "%"))
        |> (case sortOrder of
                (Just "NameAsc") -> orderByAsc #lastName
                (Just "NameDsc") -> orderByDesc #lastName
                (Just "DateAsc") -> orderByAsc  #enrollmentDate
                (Just "DateDsc") -> orderByDesc #enrollmentDate
                Nothing -> orderByAsc #lastName
                _ -> orderByAsc #lastName)
        |> fetch

However, as you can see, the ordering clause is duplicated.

If I try to factor the ordering clause out as follows:

let sortClause = (case sortOrder of
                    (Just "NameAsc") -> orderByAsc #lastName
                    (Just "NameDsc") -> orderByDesc #lastName
                    (Just "DateAsc") -> orderByAsc  #enrollmentDate
                    (Just "DateDsc") -> orderByDesc #enrollmentDate
                    Nothing -> orderByAsc #lastName
                    _ -> orderByAsc #lastName)

I (understandably) get the following:

since we're outside of the context of querying.

Update 2

OK, I was able to factor out the sort clause by having a parameter for the query that isn't referenced:

let sortClause q = (case sortOrder of
                    (Just "NameAsc") -> orderByAsc #lastName
                    (Just "NameDsc") -> orderByDesc #lastName
                    (Just "DateAsc") -> orderByAsc  #enrollmentDate
                    (Just "DateDsc") -> orderByDesc #enrollmentDate
                    Nothing -> orderByAsc #lastName
                    _ -> orderByAsc #lastName)

I can use sortClause, however, I do have to add in a "no-op" call to queryOr just to make the types line up:

students <- case searchString' of

    Nothing -> query @Student 
        |> queryOr
            (filterWhereILike (#lastName, "%"))
            (filterWhereILike (#firstMidName, "%"))
        |> sortClause (query @Student)
        |> fetch

    (Just str) -> query @Student 
        |> queryOr
            (filterWhereILike (#lastName, "%" <> str <> "%"))
            (filterWhereILike (#firstMidName, "%" <> str <> "%"))
        |> sortClause (query @Student)
        |> fetch

So, I guess for this approach the question is, is there a way to avoid having to use the "no-op" queryOr in the first branch?

user_14144232 · Answer

I could reproduce the issue. It's a bug in IHP where the type of queryOr is too specific. Fixed via https://github.com/digitallyinduced/ihp/pull/1081

Can you give that a try. See https://ihp.digitallyinduced.com/Guide/updating.html#updating-to-a-specific-git-commit for updating to a specific commit.

Unrelated to the bug here's another version of the action:

    action StudentsAction = do
        let searchString' = paramOrNothing @Text "query"
        let sortOrder = paramOrNothing @Text "sort"

        let filterWhereName = case searchString' of
                (Just searchQuery) -> filterWhere (#lastName, "%" <> searchQuery <> "%")
                Nothing -> \query -> query
        
        let orderBySortParam = case sortOrder of
                (Just "NameAsc") -> orderByAsc  #lastName
                (Just "NameDsc") -> orderByDesc #lastName
                (Just "DateAsc") -> orderByAsc  #enrollmentDate
                (Just "DateDsc") -> orderByDesc #enrollmentDate
                _                -> orderByAsc #lastName

        students <- query @Student
                |> filterWhereName
                |> orderBySortParam
                |> fetch

        render IndexView { .. }