view release on metacpan or  search on metacpan

examples/query.pl  view on Meta::CPAN

Author -> author_tag -> Tag

Many-to-many relationships provide additional opportunities for linking. For a simple author/tag link,
you could list authors which have:
* All [these tags]
* None of [these tags]
* Any of [these tags]
* One of [these tags]
Each of these modes requires grouping on the main table, after which additional operations can be
performed using aggregate functions:
* all => sum(case field when value then 1 else 0) having field > 0 for values
* none => sum(case field when value then 1 when value2 then 1 else 0) for values having sum = 0
* any => sum(case field when value then 1 when value2 then 1 else 0) for values having sum > 0
* one => sum(case field when value then 1 else 0) having (sum field) + (sum field2) = 1 for values



 select		ar.idarticle
 from		article ar
 inner join	article_author aa on aa.idarticle = ar.idarticle
 inner join	(
  	select		at.idauthor
  	from		author_tag at
  	inner join	tag t on t.idtag = at.idtag
  	group by	at.idauthor
  	having		sum(case t.name when 'staff' then 1 else 0 end) > 0
  	and		sum(case t.name when 'editor' then 1 else 0 end) > 0
 ) as x on aa.idauthor = x.idauthor
 where	title like 'Test%';

Start off with all intermediate tables included. A later optimisation pass could perhpas elide any tables
which do not contribute to the final result.

Article -> Author -> Address -> City -> Tag

Project -> Issue -> Email -> Commit -> Tag


create table author (
	idauthor bigserial primary key,
	name text
);
create index a_name on author(name);

create table article (
	idarticle bigserial primary key,
	title text,
	content text
);
create index a_title on article(title);

create table tag (
	idtag bigserial primary key,
	name text
);
create index t_name on tag(name);

create table article_tag (
	idarticle_tag bigserial primary key,
	idarticle bigint references article(idarticle) on delete cascade on update cascade,
	idtag bigint references tag(idtag) on delete cascade on update cascade
);
create index at_article on article_tag(idarticle);
create index at_tag on article_tag(idtag);
create table author_tag (
	idauthor_tag bigserial primary key,
	idauthor bigint references author(idauthor) on delete cascade on update cascade,
	idtag bigint references tag(idtag) on delete cascade on update cascade
);
create index aut_author on author_tag(idauthor);
create index au_tag on author_tag(idtag);

create table article_author (
	idarticle_author bigserial primary key,
	idarticle bigint references article(idarticle) on delete cascade on update cascade,
	idauthor bigint references author(idauthor) on delete cascade on update cascade
);
create index aa_article on article_author(idarticle);
create index aa_author on article_author(idauthor);

=cut