Обсуждение: First-class Polymorphic joins?
It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature. A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. So you could havea "tags" table, that can attach tags to any of a variety of other tables. Rails handles this by including the table nameas a string. This seems a reasonable thing to want to do, and it seems that the database could handle it by combining the fields fromthe target tables in the result. I also think migrations ought to be a first-class feature…
On 08/13/2015 05:23 PM, Guyren Howe wrote: > It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature. > > A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. I am pretty sure it already does that: http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html REFERENCES reftable [ ( refcolumn ) ] So you could have a "tags" table, that can attach tags to any of a variety of other tables. Rails handles this by including the table name as a string. > > This seems a reasonable thing to want to do, and it seems that the database could handle it by combining the fields fromthe target tables in the result. > > I also think migrations ought to be a first-class feature… So is this the push the ORM into the database day? > -- Adrian Klaver adrian.klaver@aklaver.com
On 08/13/2015 05:59 PM, Guyren Howe wrote: Ccing list > On Aug 13, 2015, at 17:49 , Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >>> A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. >> >> I am pretty sure it already does that: >> >> http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html >> >> REFERENCES reftable [ ( refcolumn ) ] > > I apologize for not being clearer. > > The point is that the fk in different rows can reference different tables. I might want to be able to attach a tag to aperson or a blog post, say. And then I want to find all the persons and blog posts with a particular tag, in a single query. Could you just not turn that around?: tag tag_id tag_desc person person_id tag_fk references tag blog blog_id tag_fk references tag > > The simplest implementation is to have a table reference as a first-class value I can store in a field. > -- Adrian Klaver adrian.klaver@aklaver.com
El 13/08/15 a las 21:23, Guyren Howe escribió: > > I also think migrations ought to be a first-class feature… What do you mean with "migrations ought to be a first-class feature"? There have been, and there still are efforts for making upgrading as smooth and simple as possible, but I'm not really sure where you want to get with this. Cheers, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 08/13/2015 05:37 PM, Martín Marqués wrote: > El 13/08/15 a las 21:23, Guyren Howe escribió: >> >> I also think migrations ought to be a first-class feature… > > What do you mean with "migrations ought to be a first-class feature"? > > There have been, and there still are efforts for making upgrading as > smooth and simple as possible, but I'm not really sure where you want to > get with this. I think Guyren is talking about something like Django or Rails migrations, Alembic, Sqitch, etc. A way to do changes to database objects either whole or as incremental changes. Basically a schema versioning method. > > Cheers, > -- Adrian Klaver adrian.klaver@aklaver.com
El 13/08/15 a las 23:17, Adrian Klaver escribió: > On 08/13/2015 05:37 PM, Martín Marqués wrote: >> El 13/08/15 a las 21:23, Guyren Howe escribió: >>> >>> I also think migrations ought to be a first-class feature… >> >> What do you mean with "migrations ought to be a first-class feature"? >> >> There have been, and there still are efforts for making upgrading as >> smooth and simple as possible, but I'm not really sure where you want to >> get with this. > > I think Guyren is talking about something like Django or Rails > migrations, Alembic, Sqitch, etc. A way to do changes to database > objects either whole or as incremental changes. Basically a schema > versioning method. OK, I misunderstood the statement. In that case, he should take a look at sqitch. Very, very nice IMO. -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hi,
You can do this today using inheritance.
define a table "tagable" with person & blog as child tables.
than you could run queries like:
select * from tags JOIN tagable on (tag_id = tag_fk);
tag_id | tag_desc | id | tag_fk | data
--------+--------------+----+--------+------------------------
3 | blog tag 1 | 1 | 3 | blog tagged by tag 1
2 | person tag 2 | 2 | 2 | person tagged by tag 2
simple :)
Regards,
- Jony
On Fri, Aug 14, 2015 at 4:04 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 08/13/2015 05:59 PM, Guyren Howe wrote:
Ccing listOn Aug 13, 2015, at 17:49 , Adrian Klaver <adrian.klaver@aklaver.com> wrote:A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.
I am pretty sure it already does that:
http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html
REFERENCES reftable [ ( refcolumn ) ]
I apologize for not being clearer.
The point is that the fk in different rows can reference different tables. I might want to be able to attach a tag to a person or a blog post, say. And then I want to find all the persons and blog posts with a particular tag, in a single query.
Could you just not turn that around?:
tag
tag_id
tag_desc
person
person_id
tag_fk references tag
blog
blog_id
tag_fk references tag
The simplest implementation is to have a table reference as a first-class value I can store in a field.
--
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