Rails - Mar 2006 - Help implementing a 43things-like ''activity'' measurement

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 Torok

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 3/4/06, Matt Torok <magicmat@gmail.com> wrote:
>
> 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 Torok
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060305/f7908190/attachment-0001.html

On 3/5/06, Manish Shah <mnshah@gmail.com> wrote:
> 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 3/4/06, Matt Torok <magicmat@gmail.com> wrote:
> >
> 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 Torok
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> 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 Torok

Matt,
> 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.
I''m a bit behind in my email, so you may''ve already received
an
answer, but have you looked at the Typo code which handles tags
(keywords)?  This does exactly what you''re looking for.

Grab a copy of the Typo source code via svn checkout
svn://typosphere.org/typo/trunk typo

Then take a look at app/models/tag.rb,
components/plugins/sidebars/tag_controller.rb and
components/plugins/sidebars/tag/content.rhtml.  This is the code which
varies the font-size of the tags based on their percent-used.

Regards,
Sean

On 3/6/06, Sean Mountcastle <smountcastle@gmail.com>
wrote:
> Matt,
>
> > 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.
>
> I''m a bit behind in my email, so you may''ve already
received an
> answer, but have you looked at the Typo code which handles tags
> (keywords)?  This does exactly what you''re looking for.
>
> Grab a copy of the Typo source code via svn checkout
> svn://typosphere.org/typo/trunk typo
>
> Then take a look at app/models/tag.rb,
> components/plugins/sidebars/tag_controller.rb and
> components/plugins/sidebars/tag/content.rhtml.  This is the code which
> varies the font-size of the tags based on their percent-used.
>
> Regards,
> Sean
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
Good idea, I''ll check that out. I haven''t used Typo before, so
I
didn''t know it had this functionality. Thanks for the pointer.
--
-Matt Torok