Sorting many-to-many database records
Posted on 2007-03-27
I have a database with one table of items with a number of many-to-many relationships associated with it. For example:
Table "Items": itemId, itemName, itemDate
Table "People": personId, personName
Table "ItemPeople": itemId, personId
Table "Categories": categoryId, categoryName
Table "ItemCategories": itemId, categoryId
In the end I'm going to be working with PHP 5 "Item" objects. But I need to be able to sort those objects based on the object properties AND the people, categories, etc associated with them. So in pseudo-SQL I want to do something like this:
FROM `Items`, `People`, `Categories`
ORDER BY `categoryName` ASC, `personName` DESC, `itemDate` ASC
(obviously the above code doesn't work--it's just to illustrate a point).
I've been playing with various types of subqueries and JOIN statements, but nothing seems to be working. Anything that looks remotely promising takes several seconds per 100 records, which is in no way acceptable. I think the problem is that I'm just not wrapping my brain around the best way to do this.
The options, as I see them are:
1. Use a fancy MySQL statement to get the correct sort
2. Grab the data via a few different statements and sort them via PHP using usort()
3. Build the object and use some kind of ItemSorter object to handle the sorting
Numbers 1 and 3 make the most sense to me, but both have their problems. Doing everything in SQL seems like it would be the most efficient if I could get the query right; MySQL was built to gather and sort data after all. Writing a sorting class also makes sense to me, but then it seems like I would have to initialize all the objects before doing the sort, which could cause problems when there are hundreds or thousands of Items to be sorted.
I KNOW other people have had to tackle this problem. Anyone have a solution or able to point me towards an article that discusses this kind of issue?