I'm trying to implement something in my app akin to 43things' main page, where more active topics are bigger than the less active topics. The difference in my app is that I'm building a business-oriented app and want to have a list of clients, where clients with recent activity are larger than others. My perhaps too-clever way of going about this is by feeding each job belonging to a client through an equation that gives an exponentially degrading score based on date of the job, then summing these scores for a final score. The equation is y=[5/(x-4)]^2, where y is the score and x is the number of weeks ago from today. My question, then, is how best to implement this. I could, of course, loop through every client, retrieve all of their jobs from the last X months, run the equation on each of them and then sum that, but that is a whole lotta database calls. Is there a way to do this with eager loading or something database-side? Or is there an altogether simpler way to achieve the same results? I'm deploying in on a Linux machine I control, so I can use any database I please so long as its free. My schema consists of a Clients table with name, address, etc. and a Jobs table with a timestamp and foreign key back to the client. -- -Matt T.
on 2006-03-05 07:49
on 2006-03-05 11:38
disclaimer: i spent like 10mins on this so forgive me if it just dumb to me it seems like this is more of a sorting problem, where the jobs list being sorted by date (most recent to most ancient) is most useful. So lets say you have all the jobs sorted by date DESC. I would create a matrix with a column for each client. Then for the rows, have a row for each week from the starting point, until the oldest week you care about. So row 1 would be 1 week from the starting point. Each i,j would be a counter So now, with your sorted jobs list, take the jobs that would fall in row 1 first. All jobs that took place within 1 week after the starting date. And increment the counter for that row, column being the client this job was for. Go through all the rows until you run out of jobs or you reach the threshhold ("i dont care about jobs that took place for any client more than 1 year ago"). Now i think you can do all sorts of tricks with the matrix. You can go through and apply a weight to those jobs in row 1, a smaller weight for row 2, and so on. In the end, sum up each column and use that weight to decide how large the client would appear in your list. I think this process would be linear in the number of jobs in regards to number of database queries. Again, if this is stupid, i apologize.
on 2006-03-05 13:15
On 3/5/06, Manish S. <firstname.lastname@example.org> wrote: > would be a counter > 2, and so on. In the end, sum up each column and use that weight to decide > I'm trying to implement something in my app akin to 43things' main > > foreign key back to the client. > Rails mailing list > email@example.com > http://lists.rubyonrails.org/mailman/listinfo/rails > > > That would be a nice, flexible way to do it (and I'd be interested to see how to do that with the DB, I'm always looking to expand my SQL knowledge. Can you post some example code?) but the problem would still remain with the DB queries. Ideally, I would want just a single query to take care of it all, since there might eventually be thousands of jobs and near a hundred clients and I'd hate to have to run that many queries every time the index is hit. I think I came up with a way to do it with sub-queries, but it would take a lot of numeric wrangling using co-efficients and date->integer casting and the like and would be a big SQL mess. The more I thought about it the more I thought that it was too clever for its own good. I think all I'm going to do now is fetch out the timestamp of the last job for all clients (which I can do with a single command using sub-queries) and just use a simple switch statement to classify each client into a class when I go to display them. It's not as cool as my first way, but it sounds like it'll work well enough. But I would be interested in seeing some example code about what you where talking about, if you have the time. It sounds like you're attacking it from a completely different angle than me, which is exciting and may lead to cool ideas. -- -Matt T.