What is an efficient, OOP way to count child records of an object?

In my current project, a District may contain zero or more School records. The Districts object acts as a collection of District, and can perform a single query to get the data behind several District in one go, rather than each District (Active Record) querying for its own data. District->schools() provides access to child schools.

On the page in question, I want to list all the districts and a count of their child schools. This is getting very sluggish as the number of districts grows, as each district must again query the schools table. Additionally, the database driver we're using does not provide a row count for the record set, so that would be another query. Some things I've considered:

  • An optional object available through a service locator which can provide counts.
  • Storing the child count with the parent record.

So, my current method of counting child records is pretty heavy. What are some ways I could lighten this load?

  • 1
    What's the database? And what's the driver? – yannis Dec 29 '11 at 13:43
  • 1
    It sounds like you're retrieving collections of all the records as objects, just to get some counts. That can be done much more easily using a query that includes Count(*) as Morons suggested: programmers.stackexchange.com/a/127562/13156 It might not be as "OOP" as you want, but some queries (especially queries like this, which perform aggregate calculations) just don't fit as well into OOP methods. – FrustratedWithFormsDesigner Dec 29 '11 at 15:03

An optional object available through a service locator which can provide counts.

Yes that works, but be sure to it in one query.. (To keep it in OO, Add a method in the appropriate Place, that runs the query and returns a collection)

Select DistrictID, D.name,count(s.SchoolID) AS CountOfSchools
    from District s
    left join School s on s.DistrictID=d.DistrictID
group by DistrictID, D.name

*This assumes each school can only be in One district.

Storing the child count with the parent record.

This Works Too! But Don't De-normalize your data, Create view (Like the above query) that your object has access to for calculated data. Note that this view is a simple one to one relationship your district table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.