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?

Answer 1

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 { .. }