Обсуждение: Hierarchical Query Question (PHP)
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.
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 idwhen 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::integerfrom gz_life_mammals mwhere taxon='Mammalia' --<< substitute meunion allselect m.taxon, m.parent_idfrom hier, gz_life_mammals mwhere m.parent=hier.taxon)select tax_rank(parent_id),count(*) num_of_descfrom hierwhere parent_id is not nullgroup by parent_idorder 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
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
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
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
);
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:
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?)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"?
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:
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?)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"?
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
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...
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
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
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
On 10/30/2015 04:38 PM, David Blomstrom wrote:
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.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--
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:
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.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?
Otherwise I suspect you will need redo your parentids.
On 10/30/2015 04:55 PM, David Blomstrom wrote:
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.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?
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?
I think I answered my question @ http://www.the-art-of-web.com/sql/lookup-table/
It sounds like the field taxon_id is similar to the field id - it's just automatically populated by a numerical key. So if I add a new taxon on row 5, then the taxon_id for the following row with change from 5 to 6 - right?
It therefore sounds like the field parent_id is useless for this particular exercise and could be deleted - unless I find some other use for it.
On 10/30/2015 05:10 PM, David Blomstrom wrote: > Just so I understand what's going on, I can create a lookup table by pasting this code... > I don't know anything about biology so this data might be laughable, but its based on your original question: http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query It seemed like the parent_id column was really the rank, and I couldn't tell if it should be taxon_rank or parent_rank. Thinking more about the problem, I normalized a little, renamed some, and came up with this script. It lacks indexes andproper names, etc, etc, because I wanted to tackle one problem at a time. Hopefully its a good example. create table rank ( id integer, descr text ); insert into rank values(1, 'Classes'); insert into rank values(2, 'Orders'); insert into rank values(3, 'Families'); insert into rank values(4, 'Genera'); insert into rank values(5, 'Species'); create table mammals ( id integer, taxon text, rank integer, parentid integer ); insert into mammals values (0, 'Chordata', 1, null); insert into mammals values (1, 'Mammalia', 1, 0); insert into mammals values (2, 'Carnivora', 2, 1); insert into mammals values (3, 'Canidae' , 3, 2); insert into mammals values (4, 'Canis' , 4, 3); insert into mammals values (5, 'Canis-lupus', 5, 4); insert into mammals values (6, 'Canis-latrans', 5, 4); -- This query shows you the basic results. It only -- returns the id columns. further queries build on this base one. -- (you could this of this query as Order Chordata :-) ) with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canis' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select * from heir; Results: id | rank | parentid ----+------+---------- 4 | 0 | 3 5 | 5 | 4 6 | 5 | 4 (3 rows) ---- -- This looks up the columns for a more meaningful result: with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canidae' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select m.taxon, r.descr from heir inner join mammals m on m.id = heir.id inner join rank r on heir.rank=r.id Results: taxon | descr ---------------+--------- Canis | Genera Canis-lupus | Species Canis-latrans | Species --------- -- This, finally, groups and counts, like your original question with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canidae' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select r.id, r.descr, count(*) from heir inner join mammals m on m.id = heir.id inner join rank r on heir.rank=r.id group by r.id, r.descr order by r.id Results: id | descr | count ----+---------+------- 4 | Genera | 1 5 | Species | 2
Awesome; thanks!
On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson <andy@squeakycode.net> wrote:
On 10/30/2015 05:10 PM, David Blomstrom wrote:Just so I understand what's going on, I can create a lookup table by pasting this code...
I don't know anything about biology so this data might be laughable, but its based on your original question:
http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
It seemed like the parent_id column was really the rank, and I couldn't tell if it should be taxon_rank or parent_rank. Thinking more about the problem, I normalized a little, renamed some, and came up with this script. It lacks indexes and proper names, etc, etc, because I wanted to tackle one problem at a time. Hopefully its a good example.
create table rank (
id integer,
descr text
);
insert into rank values(1, 'Classes');
insert into rank values(2, 'Orders');
insert into rank values(3, 'Families');
insert into rank values(4, 'Genera');
insert into rank values(5, 'Species');
create table mammals (
id integer,
taxon text,
rank integer,
parentid integer
);
insert into mammals values (0, 'Chordata', 1, null);
insert into mammals values (1, 'Mammalia', 1, 0);
insert into mammals values (2, 'Carnivora', 2, 1);
insert into mammals values (3, 'Canidae' , 3, 2);
insert into mammals values (4, 'Canis' , 4, 3);
insert into mammals values (5, 'Canis-lupus', 5, 4);
insert into mammals values (6, 'Canis-latrans', 5, 4);
-- This query shows you the basic results. It only
-- returns the id columns. further queries build on this base one.
-- (you could this of this query as Order Chordata :-) )
with recursive heir(id, rank, parentid) as (
select m.id, 0, m.parentid
from mammals m
where taxon = 'Canis'
union all
select m.id, m.rank, m.parentid
from heir
inner join mammals m on m.parentid = heir.id
)
select * from heir;
Results:
id | rank | parentid
----+------+----------
4 | 0 | 3
5 | 5 | 4
6 | 5 | 4
(3 rows)
----
-- This looks up the columns for a more meaningful result:
with recursive heir(id, rank, parentid) as (
select m.id, 0, m.parentid
from mammals m
where taxon = 'Canidae'
union all
select m.id, m.rank, m.parentid
from heir
inner join mammals m on m.parentid = heir.id
)
select m.taxon, r.descr
from heir
inner join mammals m on m.id = heir.id
inner join rank r on heir.rank=r.id
Results:
taxon | descr
---------------+---------
Canis | Genera
Canis-lupus | Species
Canis-latrans | Species
---------
-- This, finally, groups and counts, like your original question
with recursive heir(id, rank, parentid) as (
select m.id, 0, m.parentid
from mammals m
where taxon = 'Canidae'
union all
select m.id, m.rank, m.parentid
from heir
inner join mammals m on m.parentid = heir.id
)
select r.id, r.descr, count(*)
from heir
inner join mammals m on m.id = heir.id
inner join rank r on heir.rank=r.id
group by r.id, r.descr
order by r.id
Results:
id | descr | count
----+---------+-------
4 | Genera | 1
5 | Species | 2