Gossamer Forum
Home : General : Databases and SQL :

Easier way for a select?

Quote Reply
Easier way for a select?
I have something like:

Code:
ID | FatherID | Deep | Name

1 | 0 | 0 | First level
2 | 1 | 1 | Second level
3 | 2 | 2 | Third level


Now i need:

1. select Name, FatherID from cat where ID = 3;
then
2. select Name, FatherID from cat where ID = result from 1
then
3. select Name from cat where ID = result from 2

to show:

Name Level 0 > Name Level 1 > Name Level 2

For shure something like this should be in GT and will search for it in a minute, but maybe someone knows an easier query for me than this three ones?

Last edited by:

Robert: Mar 28, 2014, 8:50 AM
Quote Reply
Re: [Robert] Easier way for a select? In reply to
Sorry, what are you trying to do?

Chers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Easier way for a select? In reply to
Very easy

Cat 1 = cars
Cat 2 = small ones
Cat 3 = wheels


At the page i want to read:

Cars > Small ones > Wheels

My product has the field catid = x (wheels)
My cat table has catid, fatherid, deep (it is like in LSQL but without CatLinks, because every product is only in one cat)

So i do now:

Select Name, FatherID where catid = x
name1 = select(Name)
fid1 = select(FatherID)

Select Name, FatherID where catid = fid1
name2 = select(Name)
fid2 = select(FatherID)

select Name where catid = fid 2
name 3=select(Name)

echo "Name3 > Name 2 > Name"

I tried it with a select in a select in a select, this brings my the id from the level 1 cat for example.
But it doesnt help with more fields.

I am just curious if there is an easier way than three select. Imagine we have ten or hundred. :)
Quote Reply
Re: [Robert] Easier way for a select? In reply to
Hi,

Ok, so this isn't a GLinks table? If it was, you should be able to use the Full_Name value with a LIKE to grab what you're after. If not, then I'm not too sure sub-queries will work too well for you (especially if they are very deep). You would be better grabbing the value, then getting the next, then the next (not as elegant, but would probably be more effective processing wise)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Easier way for a select? In reply to
That is was i have done.
How GT makes this?
Quote Reply
Re: [Robert] Easier way for a select? In reply to
With GLinks, you can work out the category structure based on the Full_Name... i.e WHERE Full_Name LIKE 'Foo/%'
Quote Reply
Re: [Andy] Easier way for a select? In reply to
Yes, for shure, but how GT does it?
Say we have 28 Cats under cats under cats under cats ... then GT will do the trick 27 times like i have done it?
Or is there any trick to walk tru the database in a better and shorter way?
Quote Reply
Re: [Robert] Easier way for a select? In reply to
As I said, it uses the Full_Name. So if you were in Foo/Bar/Test, then you could do:

Code:
my @tmp;
my @cats;
foreach (split /\//, q|Foo/Bar/Test|){
push @tmp, $_;
push @cats, join("/", @tmp);
}

my $query = $DB->table("Category")->select( GT::SQL::Condition->new('Full_Name','IN',\@cats) )

As an example of course. I'm not sure how GLinks does it as I've never had a need to look at that part of the code ;)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!