Gossamer Forum
Quote Reply
Last x voted
I'm trying to work a global that will display the last x voted for with the most recent at the top of the list.

I've tried to modify an existing global using these lines but I can't get it to work. Any ideas?

Code:
my $search_db = $DB->table('Links','ClickTrack');
$search_db->select_options ("ORDER BY Created ASC","LIMIT $_[0]");
$sth = $search_db->select ( { ClickType => 'Rate','>=','1' });
Quote Reply
Re: [MJB] Last x voted In reply to
Not sure - but why are you using the below? =)

Code:
$sth = $search_db->select ( { ClickType => 'Rate','>=','1' });

...should probably be:

Code:
$sth = $search_db->select ( { ClickType => 'Rate' });

Wink

Whenever you have questions regarding the SQL query being run - this is a handy tool:

Code:
print $sth->query;

...which will print out the query that was just run Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Last x voted In reply to
Quote:
Code:
$sth = $search_db->select ( { ClickType => 'Rate' });

I tried that but it just lists the first 5 entries in the database whether they have votes or not.
Quote Reply
Re: [MJB] Last x voted In reply to
Just gotta pop out to do some gardening, but will have a look once I get back in Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [MJB] Last x voted In reply to
Hi,

Do you get the right results with this query? (in SQL Monitor)

Code:
SELECT DISTINCT(glinks_ClickTrack.LinkID) FROM glinks_ClickTrack
JOIN glinks_Links ON glinks_ClickTrack.LinkID = glinks_Links.ID
WHERE ClickType = "Hits"
ORDER BY glinks_ClickTrack.Created LIMIT 5;

If so, lemme know - and I'll write a little global to get the results for ya :)

UPDATE - that query doesn't do quite what I was expecting. Need a little time to look at other options =) (best it is can be all done in a single query, as its nicer on the server)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Last edited by:

Andy: Apr 3, 2010, 10:12 AM
Quote Reply
Re: [Andy] Last x voted In reply to
Ok, give this one a go and let me know if it pulls the right data:

Code:
SELECT DISTINCT
glinks_Links.*
FROM glinks_ClickTrack
INNER
JOIN glinks_Links
ON glinks_Links.ID = glinks_ClickTrack.LinkID
WHERE glinks_ClickTrack.ClickType = 'Hits'
ORDER
BY glinks_ClickTrack.Created LIMIT 5;

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Last x voted In reply to
Andy, don't spend too much time on this if it's going to create problems with the server. I just thought that it might be easy to adapt another global to suit but I discovered that it wasn't that simple. Some of the info that I did try made the results page hang which looks like it's hammering the server for info which isn't worth the hassle.

Last edited by:

MJB: Apr 3, 2010, 12:07 PM
Quote Reply
Re: [Andy] Last x voted In reply to
OK, a slight change to your query above returns the correct results:

Code:
SELECT DISTINCT glinks_Links.*
FROM glinks_ClickTrack
INNER
JOIN glinks_Links
ON glinks_Links.ID = glinks_ClickTrack.LinkID
WHERE glinks_ClickTrack.ClickType = 'Rate'
ORDER
BY glinks_ClickTrack.Created DESC LIMIT 5;
Quote Reply
Re: [Andy] Last x voted In reply to
Oooops! Just received this message from my host:

Quote:
Hello,

Your Goassamer Threads page.cgi was running over 15 time on the server, and each instance was trying to use about 2Gigs of memory.

We had to kill off the processes as it was overloading the server.

Please check on your code and make sure it does not keep doing this.

I've asked them to try and identify which page.cgi it was but I have one called test which I was using to test out the global.

Looks like it's time for me to quit tinkering. Blush
Quote Reply
Re: [MJB] Last x voted In reply to
Hi,

So you tried doing it in another global?

Try this one - shouldn't cause it to crash:

get_latest_rated_links
Code:
sub {
my $sth = $DB->table('Links')->do_query(qq|SELECT DISTINCT glinks_Links.*
FROM glinks_ClickTrack
INNER
JOIN glinks_Links
ON glinks_Links.ID = glinks_ClickTrack.LinkID
WHERE glinks_ClickTrack.ClickType = 'Rate'
ORDER
BY glinks_ClickTrack.Created DESC LIMIT 5|) || die $GT::SQL::error;

my @loop;
while (my $hit = $sth->fetchrow_hashref) {
push @loop, $hit;
}

return { latest_voted_loop => \@loop }

}

Code:
<%get_latest_rated_links%>
<%loop latest_voted_loop%>
<%include link.html%>
<%endloop%>

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Last x voted In reply to
Thanks Andy. Smile
Quote Reply
Re: [Andy] Last x voted In reply to
Hi Andy,
I'm using your last voted global, but sometimes it shows not the 5 last rated links, but only 1 or 2 last voted links.
Do you have an idea why...?

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Last x voted In reply to
Have you checked the ClickTrack table to make sure its not being cleared out? (I know you sometimes clear it out, to make the DailyStats plugin run quicker, instead of going back years)

Otherwise, I'm not sure - if it works most of the time, then the query must be ok - so a bit odd.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Last x voted In reply to
No I did not clear the click track table for the last month.
Seems like the global counts only the votes of the last days and clears them out if the votes are older?

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Last x voted In reply to
Hi,

Nah, there is no condition in there to only get xx last days ... it just grabs from whatever is from the table. Not really sure without having a look (and would need to be on a date when its only showing 2-3 records). Shoot me an email on a day when its only showing 2-3 entries, and I'll go in and have a look at the table (and test the query in SQL Monitor, to see why its not working right)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Last x voted In reply to
I have this problem but I think the ClickTrack table is constantly updated as it counts jump.cgi as well. As the amount of jumps increases the vote counts are moved along and eventually disappear.

I might not be 100% correct here but it certainly looks like this.

Andy, I'll e-mail you my details as I have a few votes showing at the moment.
Quote Reply
Re: [MJB] Last x voted In reply to
Hi,

Looks like its something to do with whats saved in the DB itself (not the global)

Code:
SELECT *
FROM glinks_ClickTrack WHERE ClickType = 'Rate'
ORDER
BY Created DESC LIMIT 5

..only gives one result.

Having a look at the build code, this is why it happens:


Code:
my $delete_by = GT::Date::date_get(time - 2*24*60*60, '%yyyy%-%mm%-%dd%');
my $click = $DB->table('ClickTrack');
$click->delete(GT::SQL::Condition->new('Created', '<', $delete_by));


Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!