Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Sorting new links

Aug 27, 2010, 2:33 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 2:33 AM

Post #1 of 15

Shortcut

Sorting new links

Hi Andy,
I' using this global for showing the newest links on my page.
At the moment they are sorted by ID

Sometimes users add links and I can not validate them, cause some information is missing.
When I validate these links later, they are not on the top of the page, cause they are sorted by ID.
Is there a better way for sorting the new links?
Would be great, if the last validated link is on top of the list...

Code:
sub { 
# Displays the newest links on the home page. 
my ($output,$sth,$link); 
my $search_db = $DB->table('Links'); 
$search_db->select_options ('ORDER BY ID DESC Limit 50'); 
$sth = $search_db->select ( { isNew => 'Yes', isValidated => 'Yes' }); 
while ($link = $sth->fetchrow_hashref) { 
if (length $link->{Title} > 100) { 
$link->{Title} = substr($link->{Title}, 0, 100) . '...'; 
} 
$output .= Links::SiteHTML::display ('link', $link); 
} 
return $output; 
}

Thanks
Matthias

Matthias
gpaed.de

Aug 27, 2010, 2:43 AM

Andy

Veteran / Moderator (18441 posts)

Aug 27, 2010, 2:43 AM

Post #2 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Hi,

You should try sorting by "Timestmp" instead (this is updated when the link is validated, so should be more realistic results)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Aug 27, 2010, 3:43 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 3:43 AM

Post #3 of 15

Shortcut

Re: [Andy] Sorting new links In reply to

Andy wrote:

Hi,

You should try sorting by "Timestmp" instead (this is updated when the link is validated, so should be more realistic results)

Cheers

Hi Andy,
sorting by Timestmp is giving weird results.
Some links validated long ago, have newer timestmps than other links???

Matthias
gpaed.de

Aug 27, 2010, 3:50 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 3:50 AM

Post #4 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

O.K. found the solution.
when links are modified, the timestamp ist updated, too

Matthias
gpaed.de

Aug 27, 2010, 4:00 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 4:00 AM

Post #5 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Hm, validating and modifing is updating the Timestmp field.
Is there something else updating the Timestmp field?
I have some links with new timestmp, which are validated some days ago, and not modified....?

Matthias
gpaed.de

Aug 27, 2010, 4:02 AM

Andy

Veteran / Moderator (18441 posts)

Aug 27, 2010, 4:02 AM

Post #6 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Yup, but that shouldn't be an issue (as the isNew flag should only be set for new links, and your global has that condition in)

Actually, if its just an issue of the validation date - you can use the following option to update the Add_Date when you validate a link:

Setup > Misc Options > link_validate_date

..and set to "Current Date"

This should then convert the Add_Date to the CURRENT date (i.e when it gets validated)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Aug 27, 2010, 4:11 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 4:11 AM

Post #7 of 15

Shortcut

Re: [Andy] Sorting new links In reply to

Hey, cool that's it.
There is only date, no time in the add_date.
How are links sorted, that are validated at the same day?

Matthias
gpaed.de

Aug 27, 2010, 4:14 AM

Andy

Veteran / Moderator (18441 posts)

Aug 27, 2010, 4:14 AM

Post #8 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Quote:

How are links sorted, that are validated at the same day?

Having a look at the code, it looks like its just sorted by Add_Date DESC:

Code:
    my $sth   = $db->query_sth({ 
        isValidated => 'No', 
        mh => $mh, 
        nh => $nh, 
        sb => 'Add_Date', 
        so => 'DESC' 
    });

..so if a link was submitted on the same date, I guess it would just be sorted by the ID as well (as thats the primary key).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Aug 27, 2010, 5:01 AM

Matthias70

Enthusiast (893 posts)

Aug 27, 2010, 5:01 AM

Post #9 of 15

Shortcut

Re: [Andy] Sorting new links In reply to

Hi Andy, thanks for your help!

Matthias
gpaed.de

Aug 27, 2010, 11:35 AM

brewt

Staff (4101 posts)

Aug 27, 2010, 11:35 AM

Post #10 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Anything updating the links record will bump the Timestmp field. This includes hit count updates.

Adrian

Sep 13, 2010, 11:54 AM

Matthias70

Enthusiast (893 posts)

Sep 13, 2010, 11:54 AM

Post #11 of 15

Shortcut

Re: [Andy] Sorting new links In reply to

Andy wrote:

Quote:

How are links sorted, that are validated at the same day?

Having a look at the code, it looks like its just sorted by Add_Date DESC:

Code:
    my $sth   = $db->query_sth({  
        isValidated => 'No',  
        mh => $mh,  
        nh => $nh,  
        sb => 'Add_Date',  
        so => 'DESC'  
    });

..so if a link was submitted on the same date, I guess it would just be sorted by the ID as well (as thats the primary key).

Cheers

Hi Andy,
Now my links are sorted by Add_Date and that works fine,
BUT
links validated at the same day are sorted by ID but ASC!
Should'nt they be sorted by DESC?

Matthias
gpaed.de

Sep 13, 2010, 11:57 AM

Andy

Veteran / Moderator (18441 posts)

Sep 13, 2010, 11:57 AM

Post #12 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Mmm, not sure to be honest. Maybe someone at GT can shed some light (normally the only time I do ORDER BY stuff, is in custom code - so you can control what order it comes out in)

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 13, 2010, 12:11 PM

Matthias70

Enthusiast (893 posts)

Sep 13, 2010, 12:11 PM

Post #13 of 15

Shortcut

Re: [brewt] Sorting new links In reply to

Hi Adrian,
I hope you still have a look at this thread.

That's my global.
Links are sorted by Add_Date.
The problem is, that links validated at the same day are sorted by ascending ID?
Can I change this to descending?

Code:
sub { 
# Displays the newest links on the home page. 
my ($output,$sth,$link); 
my $search_db = $DB->table('Links'); 
$search_db->select_options ('ORDER BY Add_Date DESC Limit 50'); 
$sth = $search_db->select ( { isNew => 'Yes', isValidated => 'Yes' }); 
while ($link = $sth->fetchrow_hashref) { 
if (length $link->{Title} > 100) { 
$link->{Title} = substr($link->{Title}, 0, 100) . '...'; 
} 
$output .= Links::SiteHTML::display ('link', $link); 
} 
return $output; 
}

Thanks

Matthias
gpaed.de

Sep 13, 2010, 11:54 PM

Andy

Veteran / Moderator (18441 posts)

Sep 13, 2010, 11:54 PM

Post #14 of 15

Shortcut

Re: [Matthias70] Sorting new links In reply to

Ah sorry, I thought you were trying to edit the core codes.

This should do it:

Code:
$search_db->select_options ('ORDER BY Add_Date DESC, ID DESC Limit 50');

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 14, 2010, 6:23 AM

Matthias70

Enthusiast (893 posts)

Sep 14, 2010, 6:23 AM

Post #15 of 15

Shortcut

Re: [Andy] Sorting new links In reply to

Andy wrote:

Ah sorry, I thought you were trying to edit the core codes.

This should do it:

Code:
$search_db->select_options ('ORDER BY Add_Date DESC, ID DESC Limit 50');

Cheers

Cool, that's what I want.
Thanks a lot

Matthias
gpaed.de