Gossamer Forum
Home : Products : Gossamer Links : Discussions :

multi-table query question

Quote Reply
multi-table query question
Hi,

I've seen something like this used a few times here in the forums regarding an object that queries more than one table:

my $db = $DB->table('Links','CatLinks');
$db->select ( { isValidated => 'Yes', CategoryID => '1' } );


What I'm curious about is how does the database know on which fields to join the tables for the relational query? i.e. For this example, doesn't it need to know that "ID" in the Links table maps to "LinkID" in the CatLinks table?

I'm working on a project that involves something similar, so any wisdom imparted would be greatly appreciated!

Thanks,

Matt G
Quote Reply
Re: [mglaspie] multi-table query question In reply to
Hi,

The Links SQL was written baseed on the database structure. So GT can use like that because they know that ID in the Links table maps to LinkID in the CatLinks.
I think almost code should be written from the database structure.

Cheers,

Beck

Quote Reply
Re: [Beck] multi-table query question In reply to
Hmmm...

Actually, I'm attempting this with a custom table.

So, what I'm looking to do is something like:

my $count_db = $DB->table('Links','Sponsors');
my $premiumcount = $count_db->count ( { LinkType => 'Premium', Sponsor => $sponsorid } );


In other words, I want to count how many times $sponsorid appears in the "Sponsor" column in the Links_Sponsors table, which has a structure like: LinkID|Sponsor. (There are additional fields, but which are left out of this example for simplicity's sake). But I only want to count the row if LinkType (a custom field in Links_Links) is "Premium". ('LinkID' in Links_Sponsors should map to 'ID' in Links_Links)

I'm looking at the Links_CatLinks.def file and see a setting:

'fk' => {
'Links_Links' => {
'LinkID' => 'ID',
}

Would adding something like this to my Links_Sponsors.def file allow what I'm trying to accomplish? And would a manual "hack" to a .def file survive future resyncs?

Again, any help is appreciated.

Thanks,

Matt G
Quote Reply
Re: [mglaspie] multi-table query question In reply to
Hi Matt,

It know's based on the foreign keys defined during setup. If you look in admin/Links/SQL.pm and look at the creation of the CatLinks table you'll see:

$c->fk ({ Links => { LinkID => 'ID' }, Category => { CategoryID => 'ID' }});

What this does is create two foreign keys, mapping CatLinks.LinkID => Links.ID and CatLinks.CategoryID => Category.ID. Now whenever you create a relation using:

my $relation = $DB->table('Links', 'CatLinks');

or

my $relation = $DB->table ('CatLinks', 'Category');

or even:

my $relation = $DB->table ('Links', 'CatLinks', 'Category');

it knows how to create the joins. So if I want to do a select on the table, I don't need to worry about specifying the join columns.

Yes adding that in will work, and will survive resyncs. One thing to be aware, that foreign keys are also used in deletes. If I delete a link with ID 5, it will also delete from CatLinks where LinkID = 5 as it is defined as a foreign key.

Hope this helps,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] multi-table query question In reply to
Very insightful. Thank you, Alex.

--
Matt G
Quote Reply
Re: [Alex] multi-table query question In reply to
This question is in relation (excuse the pun!) to my post under the SQL discussions on joining three tables.

See: http://www.gossamer-threads.com/...orum.cgi?post=199543

If links automatically knows the common element, using my $relation = $DB->table ('Links', 'CatLinks', 'Category'); I should be able to solve my problem. I just need a little help.

I have a custom column in Category called FCType. I want to select the links with the most hits (Link.Title) from Links, where the link's corresponding category has FCType = "something".

Crazy

my $relation = $DB->table ('Links', 'CatLinks', 'Category');
my $search_db->select_options('ORDER BY Hits DESC Limit 1');
my $sth = $search_db->select( ('Title'), {Category.FCType = "something"} );
my $title = $sth->fetchrow_array;

Someone want to help me on puting this into perl... I am nearly there with this!!


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 7, 2002, 10:43 PM
Quote Reply
Re: [Ian] multi-table query question In reply to
If you want to select the link with the most hits why did you say (Link.Title) ?...don't you mean Hits ?

To do that you'd use MAX(Hits)
Quote Reply
Re: [Paul] multi-table query question In reply to
I think I know what you mean... but I want the select to give me the title of the link with the most hits which belongs to a category which has a coloumn in it equal to something.

Crazy


I was wondering about that too... I guess

my $sth = $search_db->select( ('Title'), {Category.FCType = "something"} );

should be:

my $sth = $search_db->select( ('Link.Title'), {'Category.FCType' = "something"} );

EDIT: But WITH quotes...hmm.


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 8, 2002, 9:02 AM
Quote Reply
Re: [Ian] multi-table query question In reply to
I think I have this working now anyways!

Amazing how something looks a bit clearer shortly AFTER you post it on the forumAngelic.

Plenty of silly errors in the syntax.... which I did not see when I was writing it....


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] multi-table query question In reply to
Does it look like:

my $link_db = $DB->table('Links');
$link_db->select_options("ORDER BY Hits DESC", "LIMIT 1");
my ($title) = $link_db->select(['Title'], { FCType => 'something' })->fetchrow_array;

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] multi-table query question In reply to
Alex to the rescue... I must be getting better at this I had:

my $relation = $DB->table ('Links', 'CatLinks', 'Category');
$relation->select_options('ORDER BY Hits DESC Limit 1');
my $sth = $relation->select( ('Links.Title'), {'Category.FCType' => 'Free'} );
my $title = $sth->fetchrow_array;


I guess your first line should include the relation Alex??? I notice you use doulbe qoutes for "LIMIT 1" etc... I am slowly learning the correct way to format these statements.

Thanks!!!


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] multi-table query question In reply to
Oops, your right. I didn't realize FCType was a Category Column, I thought it was a Link column. As for quotes, I try to use single quotes for things that don't contain variables, purely "for looks". =) Don't know why I did double quotes there.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] multi-table query question In reply to
Ahh, so not essential.

It is nice to pick up these style tips from you guys who know what you are doing. Sometimes it is difficult to differentiate style from essential though, so I just ask!

I am excited by the ability to join these three tables... it opens many new doors for me. I have also seen people playing with table joins where they specify the actual commen elements joining the tables... but one step at a time... this method makes the code look very clean indeed - thanks to Links!

Thanks for the tips.

Ian


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Alex] multi-table query question In reply to
Alex,

It looks like my "specify which columns to join" has come sooner than later!!! How about this 4 table join?

I want to join Links and Reviews:

my $relation = $DB->table ('Links', 'CatLinks', 'Category', 'Reviews');
$relation->select_options("ORDER BY Review.Review_Date DESC", "LIMIT 1");
my $sth = $relation->select( ('Links.Title'), {'Category.FCType' => 'Create'} );
my $title = $sth->fetchrow_array;

to return the Link.Title for the latest Review when a link belongs to a the category with a column "FCType" equal to "Create" in this case.

Does links know how to join like this?? (review_id <=> LinkID etc) This is not just a question, I actually want to do this.Crazy


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 8, 2002, 11:39 AM
Quote Reply
Re: [Ian] multi-table query question In reply to
Can you just explain what exactly you want to do?....I just have a feeling that you don't need 4 tables to do it.
Quote Reply
Re: [Paul] multi-table query question In reply to
Sure...

I want to select the Link.Title for the newest review which corresponds to a link which is in a category which has a column FCType = "Create". Please don't ask me why, but this is what it has to do.

Gosh, writing that out, is almost is hard as writing the code!!

Thanks Paul, i'd love to know a simpler method of achieving the same result.


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 8, 2002, 11:34 AM
Quote Reply
Re: [Ian] multi-table query question In reply to
Code:
my $join = $DB->table( 'Links', 'Category', 'Reviews' );

$join->select_options( 'ORDER BY review_date DESC' );

my $record = $join->select( { FCType => "Create" }, 'Title' )->fetchrow;

I think that should work. You don't need the CatLinks table.
Quote Reply
Re: [Paul] multi-table query question In reply to
Cool, thanks Paul!!

How does Links know how to match up the Link ID with the Category ID then?Crazy
Does this mean my other functions don't need the CatLinks table as well?

Like this similar function which returns the newsest link (its title) where its corresponding category has FCType="Create"?

my $relation = $DB->table ('Links', 'CatLinks', 'Category');
$relation->select_options("ORDER BY Add_Date DESC", "LIMIT 1");
my $sth = $relation->select( ('Links.Title'), {'Category.FCType' => 'Create'} );
my $title = $sth->fetchrow_array;

i.e. my $relation = $DB->table ('Links', 'Category');


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 8, 2002, 11:46 AM
Quote Reply
Re: [Ian] multi-table query question In reply to
>>How does Links know how to match up the Link ID with the Category ID then?<<

Hmmm maybe I have it wrong then. May as well give it a try though.
Quote Reply
Re: [Paul] multi-table query question In reply to
I will give it a try, thank for the suggestion anyways Paul.

I think once your reach the forth table join (some of which are the biggest in Links).... performace is just going to have to suffer also!

Manipulating and searching for data with SQL statements is the fastest way to go right? I can see this is going to be a slow function all the same.


Edit: I wonder if there is a way of speeding up this function (table join) by limiting the number of columns in the join to just the ones I am using in the code above?.... anyone know how to do this??



http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 8, 2002, 12:02 PM