Right now, we are directly using the Organization activerecord model, this means its not possible (or performant) to do things like sort on most recent event, activity count, or user count.
We should create a view that performs this aggregation in postgres.
This view should select all of the existing fields on the organizations table along with member count, event count, and the ids and names of any child organizations.
Event count can be gotten by joining to the activities table, member count by joining to the users table and child organizations can be retrieved by selecting from the organizations table where parent_id matches id.
Roughly, you will need to
Generate the database migration and empty sql file. This can be done using the scenic library. Remember to pass ---materialized to the rails generate scenic:view comment
Develop a SQL query with the required fields. rails dbconsole in the server/ directory will open up the psql repl already connected to the database, but you are also welcome to use any other DB tools you're comfortable or familiar with.
Once you're happy with the SQL, run the migration via rails db:migrate. This will actually create the view in the database
You will want to create a model file in server/app/models/materialized_views/organization_browse_table_row.rb, you can look at the other files in that dir for examples.
Once that's completed you should be able to open up a rails console with rails c and run MaterializedViews::OrganizationBrowseTableRow.first and have it return a value. Then we can work on making this new model available via the API.
Right now, we are directly using the
Organizationactiverecord model, this means its not possible (or performant) to do things like sort on most recent event, activity count, or user count.We should create a view that performs this aggregation in postgres.
This view should
selectall of the existing fields on the organizations table along with member count, event count, and the ids and names of any child organizations.Event count can be gotten by joining to the
activitiestable, member count by joining to theuserstable and child organizations can be retrieved by selecting from the organizations table whereparent_idmatchesid.Roughly, you will need to
sqlfile. This can be done using the scenic library. Remember to pass---materializedto therails generate scenic:viewcommentrails dbconsolein theserver/directory will open up thepsqlrepl already connected to the database, but you are also welcome to use any other DB tools you're comfortable or familiar with.rails db:migrate. This will actually create the view in the databaseserver/app/models/materialized_views/organization_browse_table_row.rb, you can look at the other files in that dir for examples.Once that's completed you should be able to open up a rails console with
rails cand runMaterializedViews::OrganizationBrowseTableRow.firstand have it return a value. Then we can work on making this new model available via the API.