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.
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.
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
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.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.