Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Iterating through a table (database) ??

Alex,

What would be the best way to iterate through the database (or a table?)

Select * from Table_Name offset x limit n

Or would it be better to

Select ID from Table_name offset x limit n

Then use ->get to get each record in turn, rather than fetchrow?

Which would have the least system impact, if speed wasn't an issue?

Specifically, I need to iterate through the Rate_VoteTrack table, and re-generate the Count and Average (rating), then update the Links table with them.

Actually, I guess it would be better to iterate through the Links table, and if no Rate_VoteTrack entry existed, set Votes and Rating to '0'. If Rate_VoteTrack had a timestamp field, then any record not updated prior to the recalc run could be deleted as "orphan" ratings.

Or... would it be better to create a table relation from Links->Rate_VotesTrack

While I'm musing on this, an esoteric type question:

Rate_UserTrack is related to Rate_VoteTrack in that each entry in the Rate_UserTrack table is one partial entry in the Rate_VotesTrack. Rate_VotesTrack is actually a fully dependent table to Rate_UserTrack.

If an entry in Rate_UserTrack is deleted or altered, a record in Rate_VotesTrack needs to be altered - ie: a column value needs to be decremented (delete/change) and a column value needs to be incremented (change), as well as 2 fields recalculated.

Is it better to do this all in the code, outside of Links->SQL or, should the tables be related, and an event triggered to handle this?

I may not be explaining this right, but LinksSQL->SQL maintains table relations. Does it cover this sort of complex relationship?

Add/edit/delete->Rate_UserTrack needs to Add/update a Rate_VoteTrack entry which updates Links->votes/Ratings.

Deleting a Link record empties out CatLinks, etc, but should also empty out Rate_UserTrack and Rate_VoteTrack.

Deleting a User should do the same... otherwise there are bound to be internal problems at some point.

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Hi,

Well, the best is to always only select off what you need (i.e. if you are only recalculating rates, votes, you may just want to do a $table->select(['ID', 'Rating', 'Votes']). Avoid select * unless you need to support custom columns that you don't know what they are.

Quote:

Is it better to do this all in the code, outside of Links->SQL or, should the tables be related, and an event triggered to handle this?

MySQL doesn't support triggers, so code has to handle this. What you can do is use subclass's though. Create your module Plugins::Rate_UserTrack.pm and when creating the tables call $creator->subclass('Plugins::Rate_UserTrack'); Then when you do:

my $table = $DB->table('Rate_UserTrack');

you actually get a Plugins::Rate_UserTrack object. Then in your code, you use inheritance to override modify function:

Code:
sub modify { 
  my $rec = shift; 
  my $res = $self->SUPER::modify($rec); 
  if ($res) { 
    ... code to handle updating vote table upon modification 
  } 
}

Let me know if this makes sense,

Cheers,

Alex
--
Gossamer Threads Inc.