Gossamer Forum
Home : Products : DBMan SQL : Discussion :

relational query using Globals - Help

Quote Reply
relational query using Globals - Help
Hi,

I'm having a heck of time getting a relational query going in DBSQL 2.02 - (I'm trying to learn object-oriented Perl but it is slow-going at this point)Crazy

Basically what follows is the code that works fine in DBSQL 1.x to return a list of related sub-categories for any specific Category. I just can't seem to do it properly for a DBSQL 2.02 global.

Code:
sub build_select_Sub_Category {
# ---------------------------------------------
# Builds a list from fields in the database.
#

my $category = $in{'Category'};
my $name = 'Sub_Category_ID';
my $table1 = 'catalog';
my $table2 = 'catalog_sub_category';
my $column1 = 'Sub_Category_ID';
my $column2 = 'Sub_Category';
my $category_ID = $in{'Category_ID'};

my $query = qq!
SELECT DISTINCT $table1.$column1, $table2.$column2 FROM $table1, $table2 WHERE Category_ID = $category_ID AND $table2.$column1 = $table1.$column1 ORDER BY Sub_Category
!;
my $sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

# Make a list
while (($column1, $column2) = $sth->fetchrow_array) {
($output .= "<a href=$db_script_url?db=$db_setup&Category_ID=$category_ID&Sub_Category_ID=$column1&sb=Title&so=asc&view_records=View&nh=1>$column2</a><br><br>\n");
}
$sth->finish;
return $output;
}

What the above does (at least in DBSQL 1.x it works) is return a list of available sub-categories within a specific category. Here's what I have for now for a DBSQL 2.02 global to accomplish the same thing (it's gone through many iterations but nothing I seem to do works)
Code:
sub {
my $tags = shift;
my $id = $tags->{Category_ID};
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ("DISTINCT", "ORDER BY Sub_Category");
my $sth = $table->select (['Sub_Category_ID', 'Sub_Category'], { Category_ID => $id });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

The part I can't seem to get right is the actual $table->select statement - no matter what permutation of variables etc. I put there, I get errors/failure - most often a "GT::SQL::Relation (5539): Bad columns / column clash: columns named 'Sub_Category_ID' have been found in current relation, please qualify your expression. at (eval 10) line 6."

I realize any/or all of my syntax for this global are wrong - can someone give me a nudge in the right direction? Thanks.

easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
Hi,

To replicate:

SELECT DISTINCT $table1.$column1, $table2.$column2 FROM $table1, $table2 WHERE Category_ID = $category_ID AND $table2.$column1 = $table1.$column1 ORDER BY Sub_Category

using GT::SQL you would do:

$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select ([\"DISTINCT $table1.column1", $column2], { CategoryID => $category_ID });

Some tips:

* ->select_options is only used for things after the where clause (ORDER BY, GROUP BY, LIMIT, etc).
* The join clause is automatically added by GT::SQL assuming the tables have a foreign key defined (table1.fk1 = table2.pk).
* Variables are automatically auto completed so you don't need to specify table.col, just col will do (unless you have duplicate column names).
* To do the distinct, you need to make a reference to it: \"DISTINCT $table1.$column1". This means GT::SQL will not quote it, and won't try to auto complete it. You could put any function you like in here.

Hope that helps,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] relational query using Globals - Help In reply to
Quote:
using GT::SQL you would do:

$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select ([\"DISTINCT $table1.column1", $column2], { CategoryID => $category_ID });

Okay Alex, here is what I have now in my global :

sub {
my $tags = shift;
my $id = $tags->{Category_ID};
in $tags
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select [\"DISTINCT catalog.Sub_Category_ID", Sub_Category], { Category_ID => $category_ID });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

Unfortunately I get one of the same results I've been getting :
"Unable to compile 'sub_category_list'. Reason: at /home/house/www/admin/Dbsql/Home.pm line 1133."
easy does it
Post deleted by Bearwithme In reply to
Quote Reply
Re: [Alex] relational query using Globals - Help In reply to
Quote:
* The join clause is automatically added by GT::SQL assuming the tables have a foreign key defined (table1.fk1 = table2.pk).

Okay, Am I to understand that you need to do this manually by editing the def files for the respective tables? Thinking this might be the problem, I edited my def files for those respective tables and added FK info. Then ran a re-sync of def files. I get this error in the Results Screen every time :
DbsqlList .. failed: Failed to execute query: 'DESCRIBE DbsqlList' Reason: Table 'house.DbsqlList' doesn't exist

All the rest of the results report back ok for each of the "default" DBSQL tables - my tables are never listed in the re-sync results screen - and yes I used the "create" function so that I could manage my tables under DBSQL 2.x which did create the appropriate def files in the def directory.

Bottom line is that the relational global is still not working - get the same error referenced in above message every time. Any ideas - and many thanks for your assistance Alex as well as to TheStoneWink


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
*bubble*


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
*riser*
easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
Hi,

Sorry about the late reply! You have some syntax errors in your code. Try:

Code:
sub {
my $tags = shift;
my $id = $tags->{Category_ID};
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select ([\"DISTINCT catalog.Sub_Category_ID", Sub_Category], { Category_ID => $category_ID });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

Looks like it should work.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] relational query using Globals - Help In reply to
Nope, sorry Alex still doesn't work...Here's what I have (I cut and pasted what you have above)

Code:
sub {
my $tags = shift;
my $id = $tags->{Category_ID};
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select ([\"DISTINCT catalog.Sub_Category_ID", Sub_Category], { Category_ID => $category_ID });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

Also can you address the other errors I mentioned when trying to re-sync def files???? Could this be part of the problem?

Thanks...


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
**bubble**


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
**riser** Hi Alex...SOS
easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
Hi ,

Could you paste the errors here so we can take a look of what happened?

Cheers,
jean

Last edited by:

jean: Nov 23, 2001, 10:01 AM
Quote Reply
Re: [jean] relational query using Globals - Help In reply to
Quote:
Could you paste the errors here so we can take a look of what happened?

Sure thing Jean...here you go :

Unable to compile 'sub_category_list'. Reason: at /home/house/www/admin/Dbsql/Home.pm line 1133.

sub_category_list is the name of my global that I'm trying to use...Any assistance on getting this going will be immensely appreciated. If you need any admin login info or anything further just say the word. Thanks Jean.


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
Yes, I need your admin info , please send me via private message and I will help you to fix them

Cheers,
Jean
Quote Reply
Re: [jean] relational query using Globals - Help In reply to
Hi,

I fixed in your server, this problem happens when you don't have a correct foreign key for your tables, the fk should be defined like this structure (in your def files):
'fk' => {
'remote_table' => {
'this_column' => 'remote_column'
},
and below is the code;
Code:
sub {
my $tags = shift;
my $id = $tags->{Category_ID};
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ('ORDER BY Sub_Category');
my $sth = $table->select ([\"DISTINCT catalog.Sub_Category_ID", 'Sub_Category'],{ Category_ID => $id });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

Cheers,
jean
Quote Reply
Re: [jean] relational query using Globals - Help In reply to
Quote:
I fixed in your server, this problem happens when you don't have a correct foreign key for your tables, the fk should be defined like this structure (in your def files):

Thank you very much Jean...It works great now! Much appreciate your efforts and help.


easy does it
Quote Reply
Re: [Bearwithme] relational query using Globals - Help In reply to
I've read this thread over and over and I'm still confused. How do you define the foreign key, in the defs file or in Admin -> Templates -> Globals, or both?

Also, do you have to create two columns for a foreign key or one? Using the example here...

Code:
'fk' => {
'remote_table' => {
'this_column' => 'remote_column'
},

Are fk and this_column the same column or two different ones?