Hierarchical Query Question (PHP)

David,

Does wrapping the transaction with BEGIN; COMMIT; work as you would expect?

$sql = "BEGIN; with recursive hier(taxon,parent_id) as (
select m.taxon, null::integer
from gz_life_mammals m
where taxon='Mammalia' --<< substitute me
union all
select m.taxon, m.parent_id
from hier, gz_life_mammals m
where m.parent=hier.taxon
)
select tax_rank(parent_id),
count(*) num_of_desc
from hier
where parent_id is not null
group by parent_id
order by parent_id; COMMIT;";

On Thu, Oct 29, 2015 at 8:18 PM, David Blomstrom <david.blomstrom@gmail.com>
wrote:

Can anyone tell me how to write the query described @
http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
?

The answer's very thorough, but I don't know how to string two queries and
a function together like that. This doesn't work:

$sql = "select * from gz_life_mammals;";

create function tax_rank(id integer) returns text as $$
select case id
when 1 then 'Classes'
when 2 then 'Orders'
when 3 then 'Families'
when 4 then 'Genera'
when 5 then 'Species'
end;
$$ language sql;

$sql = "with recursive hier(taxon,parent_id) as (
select m.taxon, null::integer
from gz_life_mammals m
where taxon='Mammalia' --<< substitute me
union all
select m.taxon, m.parent_id
from hier, gz_life_mammals m
where m.parent=hier.taxon
)
select tax_rank(parent_id),
count(*) num_of_desc
from hier
where parent_id is not null
group by parent_id
order by parent_id;";

Thanks.

--
Jason O'Donnell
Crunchy Data Solutions

No, I get the same T_FUNCTION error.

Someone commented that the function...

create function tax_rank(id integer) returns text as $$
select case id
when 1 then 'Classes'
when 2 then 'Orders'
when 3 then 'Families'
when 4 then 'Genera'
when 5 then 'Species'
end;
$$ language sql;

...should ideally be part of the table schema. Does that mean I need to go
into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result as
"20 families, 74 genera and 413 species." With MySQL I could probably turn
that into a series of echo values, which I could then display like this:

echo ''.$NumberChildren.' families<br>
'.$NumberGrandchildren.' genera<br>
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.

On 10/29/2015 7:18 PM, David Blomstrom wrote:

Can anyone tell me how to write the query described @
http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
?

The answer's very thorough, but I don't know how to string two queries
and a function together like that. This doesn't work:

$sql = "select * from gz_life_mammals;";

create function tax_rank(id integer) returns text as $$
select case id
when 1 then 'Classes'
when 2 then 'Orders'
when 3 then 'Families'
when 4 then 'Genera'
when 5 then 'Species'
end;
$$ language sql;

$sql = "with recursive hier(taxon,parent_id) as (
select m.taxon, null::integer
from gz_life_mammals m
where taxon='Mammalia' --<< substitute me
union all
select m.taxon, m.parent_id
from hier, gz_life_mammals m
where m.parent=hier.taxon
)
select tax_rank(parent_id),
count(*) num_of_desc
from hier
where parent_id is not null
group by parent_id
order by parent_id;";

Thanks.

The function is created once (like with your create tables). Don't use
it in PHP.

Your PHP should only be like:

$sql = "with recursive hier(taxon,parent_id) as (
select m.taxon, null::integer
from gz_life_mammals m
where taxon='Mammalia' --<< substitute me
union all
select m.taxon, m.parent_id
from hier, gz_life_mammals m
where m.parent=hier.taxon
)
select tax_rank(parent_id),
count(*) num_of_desc
from hier
where parent_id is not null
group by parent_id
order by parent_id;";

$result = pg_query($dbh, $sql);

while ($row = pg_fetch_array($result)) {
etc
etc

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/30/2015 3:47 PM, David Blomstrom wrote:

No, I get the same T_FUNCTION error.

Someone commented that the function...

create function tax_rank(id integer) returns text as $$
select case id
when 1 then 'Classes'
when 2 then 'Orders'
when 3 then 'Families'
when 4 then 'Genera'
when 5 then 'Species'
end;
$$ language sql;

...should ideally be part of the table schema. Does that mean I need to
go into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result
as "20 families, 74 genera and 413 species." With MySQL I could probably
turn that into a series of echo values, which I could then display like
this:

echo ''.$NumberChildren.' families<br>
'.$NumberGrandchildren.' genera<br>
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.

Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

I wonder if the function is even necessary.

Correct, its not. It should probably be a lookup table:

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/30/2015 4:36 PM, Andy Colson wrote:

On 10/30/2015 3:47 PM, David Blomstrom wrote:

No, I get the same T_FUNCTION error.

Someone commented that the function...

create function tax_rank(id integer) returns text as $$
select case id
when 1 then 'Classes'
when 2 then 'Orders'
when 3 then 'Families'
when 4 then 'Genera'
when 5 then 'Species'
end;
$$ language sql;

...should ideally be part of the table schema. Does that mean I need to
go into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result
as "20 families, 74 genera and 413 species." With MySQL I could probably
turn that into a series of echo values, which I could then display like
this:

echo ''.$NumberChildren.' families<br>
'.$NumberGrandchildren.' genera<br>
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.

Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

I wonder if the function is even necessary.

Correct, its not. It should probably be a lookup table:

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

-Andy

Humm, after looking at this further, my answer isn't right. I did not
notice rank (classes, orders, families...) is different than taxon
(mammilia, carnivora, ...)

But still, lookup table is better than function.

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Just so I understand what's going on, I can create a lookup table by
pasting this code...

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the moment,
and it will be at least a few weeks before I can get it fixed.) And this
script will create TWO tables - gz_life_mammals and a matching "lookup
table"?

On 10/30/2015 04:10 PM, David Blomstrom wrote:

Just so I understand what's going on, I can create a lookup table by
pasting this code...

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the
moment, and it will be at least a few weeks before I can get it
fixed.) And this script will create TWO tables - gz_life_mammals and a
matching "lookup table"?

If I'm following correctly it will create two tables, the first is the
definition of the lookup table. You should add an index its taxonid (if
'serial' doesn't imply that?)

In the second table (gz_lief_mamals) have taxonid and parentid as
"foreign keys" to taxon.taxonid.

The gui side of pgAdmin3 should help out here, but it's been quite a
while since I was there.

On 10/30/2015 04:10 PM, David Blomstrom wrote:

Just so I understand what's going on, I can create a lookup table by
pasting this code...

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the
moment, and it will be at least a few weeks before I can get it
fixed.) And this script will create TWO tables - gz_life_mammals and a
matching "lookup table"?

If I'm following correctly it will create two tables, the first is the
definition of the lookup table. You should add an index its taxonid (if
'serial' doesn't imply that?)

In the second table (gz_lief_mamals) have taxonid and parentid as
"foreign keys" to taxon.taxonid.

The gui side of pgAdmin3 should help out here, but it's been quite a
while since I was there.

On 30/10/2015 22:10, David Blomstrom wrote:

Just so I understand what's going on, I can create a lookup table by
pasting this code...

create table taxon (
taxonid serial,
descr text
);
create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the
moment, and it will be at least a few weeks before I can get it fixed.)
And this script will create TWO tables - gz_life_mammals and a matching
"lookup table"?

Yes, it will. I haven't seen, what went before in this thread so may
have missed stuff, but you should also add a foreign key constraint
between the tables (for taxonid anyway, dunno what parentid refers to):

create table gz_life_mammals (
id serial,
taxonid integer, -- use the lookup table
parentid integer, -- use the lookup table

constraint mammals_taxon_fk foreign key (taxonid) references
taxon(taxonid)

);

If parentid also references taxon.taxonid, add a similar constraint for
it too,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing Edit > Object

New Table

But is there a pace for me to past in a block of code that creates the
table with just one process? If I click on the SQL icon, a SQL window opens
up, and I can past the code into SQL Editor, but I don't know how to
"activate" it; pressing ENTER does nothing.

I'm following a pgAdmin tutorial, but this page appears to focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html

On 30/10/2015 22:29, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing Edit >
Object > New Table

But is there a pace for me to past in a block of code that creates the
table with just one process? If I click on the SQL icon, a SQL window
opens up, and I can past the code into SQL Editor, but I don't know how
to "activate" it; pressing ENTER does nothing.

There's an "Execute query" button in the toolbar, and pressing F5 does
the same. If the toolbar isn't visible, you can make it visible under
the "View" menu.

As a bonus, you can select a block of SQL and only the selected code
will get executed.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/30/2015 03:29 PM, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing Edit >
Object > New Table

But is there a pace for me to past in a block of code that creates the
table with just one process? If I click on the SQL icon, a SQL window
opens up, and I can past the code into SQL Editor, but I don't know how
to "activate" it; pressing ENTER does nothing.

http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.

I'm following a pgAdmin tutorial, but this page appears to focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ah, yes - "Execute SQL." It created the table this time. Awesome.

One other question - when I close the SQL window, it asks me if I want to
save the file. Is there any special reason for saving it? It looks like it
simply saved a copy of the query I executed.

On Fri, Oct 30, 2015 at 3:36 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/30/2015 03:29 PM, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing Edit >
Object > New Table

But is there a pace for me to past in a block of code that creates the
table with just one process? If I click on the SQL icon, a SQL window
opens up, and I can past the code into SQL Editor, but I don't know how
to "activate" it; pressing ENTER does nothing.

http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.

I'm following a pgAdmin tutorial, but this page appears to focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

On 10/30/2015 04:38 PM, David Blomstrom wrote:

Ah, yes - "Execute SQL." It created the table this time. Awesome.

One other question - when I close the SQL window, it asks me if I want
to save the file. Is there any special reason for saving it? It looks
like it simply saved a copy of the query I executed.

On Fri, Oct 30, 2015 at 3:36 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/30/2015 03:29 PM, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing
Edit >
Object > New Table

But is there a pace for me to past in a block of code that
creates the
table with just one process? If I click on the SQL icon, a SQL
window
opens up, and I can past the code into SQL Editor, but I don't
know how
to "activate" it; pressing ENTER does nothing.

http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.

I'm following a pgAdmin tutorial, but this page appears to
focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org <http://www.geobop.org&gt;

This goes back to the issue of "scripting". Must of us would rather
save the sql in a file and have it to re-run, edit, re-run as needed.
Then add it version control. But that takes a working terminal.

Yes, I guess it does make sense to keep a copy of your actions.

In the meantime, I now have two new tables with the following schema:

-- Table: public.taxon

-- DROP TABLE public.taxon;

CREATE TABLE public.taxon
(
taxonid integer NOT NULL DEFAULT nextval('taxon_taxonid_seq'::regclass),
descr text
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.taxon
OWNER TO postgres;

* * * * *

-- Table: public.gz_life_mammals

-- DROP TABLE public.gz_life_mammals;

CREATE TABLE public.gz_life_mammals
(
id integer NOT NULL DEFAULT nextval('gz_life_mammalsx_id_seq'::regclass),
taxonid integer,
parentid integer
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
OWNER TO postgres;

The field descr would presumably hold the values I originally had in the
field Taxon - e.g. the names of various taxons, like 'Mammalia' and
'Canis-lupus.' The field id is just a numerical key, and I already have the
numerical values for parentid.

But what am I supposed to put in the field taxonid?

On 10/30/2015 04:55 PM, David Blomstrom wrote:

The field descr would presumably hold the values I originally had in
the field Taxon - e.g. the names of various taxons, like 'Mammalia'
and 'Canis-lupus.' The field id is just a numerical key, and I already
have the numerical values for parentid.

But what am I supposed to put in the field taxonid?

Hm, you might want to take a step back. What does the parentid field
you already have represent. Is it you mysql version of the data. Does
it point to an id of a taxon from the version? If so you need to
redefine the taxon table to just integer, not serial and load that table
from you csv dump of mysql.

Otherwise I suspect you will need redo your parentids.

On 10/30/2015 04:55 PM, David Blomstrom wrote:

The field descr would presumably hold the values I originally had in
the field Taxon - e.g. the names of various taxons, like 'Mammalia'
and 'Canis-lupus.' The field id is just a numerical key, and I already
have the numerical values for parentid.

But what am I supposed to put in the field taxonid?

I'm going to shut up - because the OP might be better served by a single
voice and other on this thread are surely more capable.

Sorry, I don't think I specified that at the beginning.

My original/master table has several fields, four of which are relevant to
this hierarchical stuff - id, taxon, parent, parent_id.

The first is a numerical key, from 1 to probably somewhere around 8,000 for
mammals, 1 to 10,000 for birds, etc. The field taxon holds the names of
taxons (e.g. 'Mammalia', 'Canis-lupus'). The field parent holds the names
of each taxon's parent. The field parent_id is just a series of numerals
matching each taxonomic level. The value is 1 for Mammalia, 2 for each
mammal order, 3 for each family, 4 for genera and 5 for species. It looks
something like this:

ID | TAXON | PARENT | PARENT_ID
1 | Mammalia | Chordata | 1
2 | Carnivora | Mammalia | 2
3 | Canidae | Carnivora | 3
4 | Canis | Canidae | 4
5 | Canis-lupus | Canis | 5
6 | Canis-latrans | Canis | 5

I wonder if that's what you had in mind with taxonid. Should I just rename
parent_id to taxon_id?