Is a sql Group by possible when accessing Anvil Tables

Anvil Q&A
#1

I have a table that has Lunch order for various schools I want to get a total of the lunches ordered for the date range input.

What I’ve tried and what’s not working:
This is What I’m doing now. It runs but back on the client side when I assign “data” to my repeating panel, I don’t see any data, However, My total count is correct.
What I’m looking for is a tables that displays 2 columns. School Name and Count

Thanks, Bob

Code Sample:

# code snippet
totalLunchCount = len(app_tables.booked_lunches.search())   
    data = []
    schoolList = app_tables.schooldropdown.search()
    for school in schoolList:
      sList = {}
      schoolName = school['Schools']
      print(schoolName)
      schoolCount = len(app_tables.booked_lunches.search(
          order_date = q.between(fromDate,toDate,min_inclusive = True,max_inclusive = True),School = schoolName))
      sList[schoolName] = schoolCount
      data.append(sList)
    print(data)
    return data, totalLunchCount

Clone link:
share a copy of your app

1 Like
#2

Hey Bob,

It’s easier to read the code if you fill out the template that was provided to you. That is, place code in triple backtics for nicer formatting. A clone is also helpful for others to assist more easily.

1 Like
#3

Sorry, New here. I have student data in my app so I don’t want to give a clone. I’m assuming data transfers as well. I’ll look and try to post in the way you suggest.

1 Like
#4

It’s no problem at all! Sometimes a simple clone can be done up just to demonstrate the issue. So certainly no need to share your actual app/data.

#5

It’s hard to say without a screenshot of what you are actually seeing/a clone but I’m wondering if your returned data should take this form instead (assuming I understand your code correctly):

data=[
{'name': 'school_1', 'another_key': 'another_val_1'}, 
{'name': 'school_1', 'another_key': 'another_val_2'}]

Then, in the client you can bind to those keys if you are, for example, setting the items on a repeating panel. You can search for data binding in the docs/examples/forum to see how that is done but hopefully that helps somewhat.

2 Likes
#6

campopianop, Thanks, I appreciate your time. My problem is that the table I have is a lunch order table so every time a student orders lunch it’s recorded as a single row…School, student name, date etc
For the report, I’ll have the school name but need to count the number of records in the table for that school in the date ranges provided to me by the user. I know how I would do this in sql, but I’m struggling to do it in Anvil. I have an Education License.

I’ll look at your example and see if I can figure it out. If not, I’ll delete my student data and post a clone.

Thanks again for your time.

Bob

#7

You’re welcome! Yes please post a clone and try the suggestion. One of the issues you mentioned was that you didn’t “see any data”. It occurred to me that perhaps you were not returning data in the correct format, as expected for data binding. Others who are smarter than me appear to agree and so that is at least one possible approach to try.