Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Bulk Moving of Links?

Quote Reply
Bulk Moving of Links?
Ok... with the category relation stored in a separate table, you need to JOIN the tables to find the links/category you need.

What I'm trying to do is:

Update CatLinks
Set CatLinks.CategoryID = 18

where

Select * from CatLinks, Links
where Domain_Name LIKE '%postcard%'
and CatLinks.CategoryID=1
and CatLinks.LinkID = Links.ID

See the problem? I need to find all the links with "postcard" in their name, and move them from one category to another.

This doesn't seem possible with SQL. It looks like it needs to have a separate routine to manage this sort of thing.

The two SQL statements are correct, the problem is I need the first one to execute only if the condition of the second is met.

BTW... the javascript "move" links seems to work ok, but it's too slow to move 100+ links.



PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ


Quote Reply
Re: Bulk Moving of Links? In reply to
Hi,

Unfortunately MySQL does not support sub-selects so this isn't possible in one query. What you could do is:

my $db = $DB->table('Links','CatLinks');
my $sth = $db->select ( GT::SQL::Condition->new('Domain', 'LIKE', '%postcard%', 'CategoryID', '=', 1), ['LinkID']);
my $str = '(' . join (',', map { $_->[0] } @{$sth->fetchall_arrayref}) . ')';
$db->update ( { CategoryID => 18 }, GT::SQL::Condition->new('LinkID', 'IN', \$str));

What that means is:

1. Get a Links,CatLinks relation object.
2. Query the database and get a list of all LinkID's where their Domain is like '%postcard%' and are in category 1.
3. Join the matching ID's into a string like '1,2,3,4'
4. Update the matching ID's and set their category id to the new value (translates to: UPDATE CatLinks SET CategoryID = 18 WHERE LinkID IN (1,2,3,4))

Yes, this would be useful to build into the program, but it will have to wait till the next version or done as a plugin as I'm trying to release the current version.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Bulk Moving of Links? In reply to
Oh,

I'm the _last_ one telling you to add features to the current version :)

But, we should be on Links 5.0 by now ;)

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ