A design desicison regarding loading collection of dependent domain models

PHP
1

Hi there, its Hall of Famer again asking questions about ORM, domain models and its practices, something I used to do a lot a few months earlier(looking for a job now so I’ve been lurking mostly). I will explain the problem I have right now, the fact is that I have a User domain model and UserProfile domain model, and apparently the latter depends on the existence of the former. The data for the two domain models are stored in separate SQL tables, and when User object is created it initially does not load UserProfile(a practice known as lazy-load) since data/information for user profile is not always needed. When a user profile object is needed(from user profile page or user control panel page), an additional trip to the database will be made to retrieve profile information for the corresponding user.

However, it become a bit tricky when I have to pull a collection of user profiles(say its a memberlist page), lazy-loading for N users will result in N trips to the database, which is very costly in terms of performance. So I am thinking about alternative methods I can use to pull collection of dependent domain objects. Let’s say I have a memberlist page that current displays user with ID 2, 3, 4, 5 and 7(a typical memberlist page has 15 users per page, but to make it 5 here as an example), there are two possible ways to solve the problem and make the trip to database once for good. One way is to just load every user profile, and loop through the User Object to attach its User Profile object by ID matches. Another way is to dynamically build a SQL query with a long whereclause(in this case ‘WHERE userid = 2 or userid = 3 or userid = 4 or userid = 5 or userid =7’) so only User Profile that matches with a User Object will be loaded. Both seem to have issues, the first method may load too many UserProfile objects that aint really needed, the second method may construct a very long SQL query that makes the trip to database expensive. I dunno which approach is better.

So I wonder, what do you advanced programmers recommend me to do with this situation? I know its strange, but it is indeed a problem I have to solve.

2

SELECT * FROM profile WHERE userid IN (2,3,4,5,7);

I routinely query hundreds of records this way in one shot. Your outgoing sql statement will be quite short as compared to the incoming data records. Just be sure of the source of your id’s so nothing nasty get’s injected.

3

You can actually shorten the WHERE clause a bit by using the IN operator to supply a comma-separated list of IDs:

WHERE userid IN (2,3,4,5,7)

Edit: pipped to the post :wink:

4

Thanks so much both of you, I think a Where In clause is probably the best solving this problem. Guess all I have to do is to make sure the user collection knows how to generate the WHERE IN clause, should be very easy with my QueryBuilder object.