Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: UNIQUE?

May 7, 2003, 7:32 AM

Andy

Veteran / Moderator (18441 posts)

May 7, 2003, 7:32 AM

Post #1 of 17

Shortcut

UNIQUE?

Does anyone know how to use a 'UNIQUE' feature in Links SQL's GT::SQL ? I currently have;

Code:
   # first of all, we need to get the list of categories... 
     my $table    = $DB->table('News'); 
     $table->select_options ('ORDER BY Category ASC'); 
     my $sth2  = $table->select();      
     my ($cats,@cats); 

    # set $got to true if we managed to find an entry matcinhg their details... 
     my $count = 0; 
     while (my $hit = $sth2->fetchrow_hashref) { 

        if ($count == 0) {  
           push(@cats,$hit->{Category}); 
        } 

        foreach (@cats) {  
           chomp; 
           if ($hit->{Category} eq $_) { next; } else { push(@cats,$hit->{Category}); } 
        } 

     }

The problem with this is that it takes ages to load, an sometimes times out (over 100 categories). Is there a better/faster way to do this? I know 'unique' would do the job....but I can't find any documentation on itworking with GT::SQL Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 7, 2003, 7:55 AM

Paul

Veteran (19537 posts)

May 7, 2003, 7:55 AM

Post #2 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

It's not hard to find ;)

GT::SQL::Creator
GT::SQL::Editor

May 7, 2003, 8:02 AM

Andy

Veteran / Moderator (18441 posts)

May 7, 2003, 8:02 AM

Post #3 of 17

Shortcut

Re: [Paul] UNIQUE? In reply to

Yeah, but I don't want to modify a field so that it only allows unique names; nor do I want to drop or add Wink

I simply want to select only unique entries for the 'Category' field :/

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 7, 2003, 8:34 AM

Paul

Veteran (19537 posts)

May 7, 2003, 8:34 AM

Post #4 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

Why didn't you say so Smile

Code:
$table->select( 'DISTINCT(Name)' )

May 7, 2003, 9:29 AM

Andy

Veteran / Moderator (18441 posts)

May 7, 2003, 9:29 AM

Post #5 of 17

Shortcut

Re: [Paul] UNIQUE? In reply to

Wow...thats simple! Thanks loads, I'm sure that feature is going to be very useful in the future too Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 8, 2003, 6:44 AM

Andy

Veteran / Moderator (18441 posts)

May 8, 2003, 6:44 AM

Post #6 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

When using the following;

my $sth2 = $table->select( 'DISTINCT(Title)', [ 'AccessString' ], $cond );

I get duplicated. Am I formatting the query wrong? I don't get any errors, and both Title and AccessString are accessable Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 8, 2003, 12:44 PM

Mel_g

Staff (79 posts)

May 8, 2003, 12:44 PM

Post #7 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

Just do:

$sth = $table->select(['distinct(Title)'],$cond);
Mel Goulet
Developer - Gossamer Threads, Inc.

May 9, 2003, 1:21 AM

Andy

Veteran / Moderator (18441 posts)

May 9, 2003, 1:21 AM

Post #8 of 17

Shortcut

Re: [Mel_g] UNIQUE? In reply to

Yeah, I tried that before, but then the field 'AccessString' is not available Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 9, 2003, 4:07 AM

afinlr

Veteran (1921 posts)

May 9, 2003, 4:07 AM

Post #9 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

$sth = $table->select(['distinct(Title)','AccessString'],$cond);

May 9, 2003, 5:45 AM

Andy

Veteran / Moderator (18441 posts)

May 9, 2003, 5:45 AM

Post #10 of 17

Shortcut

Re: [afinlr] UNIQUE? In reply to

Already tried that. This is why I'm so confused Crazy

If I use the above example, it gives me both 'AccessString' and 'Title', but duplicates occur. If I remove the ,'AccessString' part, it doesn't give duplicates, but AccessString is not available Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 9, 2003, 6:06 AM

afinlr

Veteran (1921 posts)

May 9, 2003, 6:06 AM

Post #11 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

Try this

$sth = $table->select([\'Distinct Title','AccessString'],$cond);

May 9, 2003, 6:20 AM

Andy

Veteran / Moderator (18441 posts)

May 9, 2003, 6:20 AM

Post #12 of 17

Shortcut

Re: [afinlr] UNIQUE? In reply to

No results are returned when using the above code Frown

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 9, 2003, 6:28 AM

afinlr

Veteran (1921 posts)

May 9, 2003, 6:28 AM

Post #13 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

Well that's what I use in my code and it works for me Unsure

. I'm out of suggestions now sorry.

May 9, 2003, 6:49 AM

Andy

Veteran / Moderator (18441 posts)

May 9, 2003, 6:49 AM

Post #14 of 17

Shortcut

Re: [afinlr] UNIQUE? In reply to

I've managed to get around the problem by opening up a new select searching for a link that has the same title, and grabbing all the variables. Seems to be working well :) (although it obviously slows it down a bit more).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Oct 26, 2003, 10:33 AM

afinlr

Veteran (1921 posts)

Oct 26, 2003, 10:33 AM

Post #15 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

Andy,

I know this thread is old but having encountered exactly the same problem I read up on this a bit more.

It seems that DISTINCT applies to the whole row not the column (as far as I can see - someone please correct me if I'm wrong). So DISTINCT(Column) is misleading - it will actually search for distinct rows. This is fine if you are only returning one column. However, it is no good if you want several columns returned.

Suppose, for example, you have two columns

Col1 Col2

a 1

a 2

select (['DISTINCT Col1']) would just return 'a' but

select (['DISTINCT Col1', 'Col2']) would return both rows as it is actually looking for distinct Col1,Col2 combinations.

I assume that your column AccessString must have had different values for the same Title?

Laura.

Oct 27, 2003, 1:26 AM

Andy

Veteran / Moderator (18441 posts)

Oct 27, 2003, 1:26 AM

Post #16 of 17

Shortcut

Re: [afinlr] UNIQUE? In reply to

What does this give you?

select (['DISTINCT Col1,Col2']);

If you look at your codes, it is running the DISTINCT on Col1, and then Col2 isn't actually assigned to anything, as its in seperate quotes.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Oct 27, 2003, 2:45 PM

afinlr

Veteran (1921 posts)

Oct 27, 2003, 2:45 PM

Post #17 of 17

Shortcut

Re: [Andy] UNIQUE? In reply to

I found this by playing around with it in SQL Monitor rather than using the GT code.

It isn't running DISTINCT on Col1, it is running it on the whole row - that's the point. DISTINCT only applies to the row and not the column whether you specify it as DISTINCT Col1, Col2 or DISTINCT(Col1), Col2 - or any other way.