Re: Query about foreign key details for php framework

Ah sorry adrian,

I am a little in the dark as well since this is just a broken piece of ORM
i am attempting to fix, in the framework. So, maybe if you could help to
reproduce that select list as a start that would be great. But, I am
suspecting they were trying to pull similar datasets from mysql or postgres
as an end goal.

On Fri, 26 Feb 2016 at 13:12 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 02/25/2016 05:36 PM, David Binney wrote:
> > Hey Adrian,
> >
> > In this case the type is just being referenced from the
> > "constraint_type" column in the "information_schema.table_constraints".
> >
> > select * from information_schema.table_constraints tc where table_name =
> > 'products';
> >
> > Hopefully that helps
>
> My confusion is that in your original post you said:
>
> "... get the same dataset from mysql and postgres ..."
>
> and what you are asking for is different datasets. Just trying to pin
> down exactly the data you need and whether it really is the same for
> MySQL and Postgres or if it is different?
>
> >
> > On Fri, 26 Feb 2016 at 11:06 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 02/25/2016 04:56 PM, David Binney wrote:
> > > Hey Adrian,
> > >
> > > rc.constraint_name AS name,
> > > tc.constraint_type AS type,
> > > kcu.column_name,
> > > rc.match_option AS match_type,
> > > rc.update_rule AS on_update,
> > > rc.delete_rule AS on_delete,
> > > kcu.table_name AS references_table,
> > > kcu.column_name AS references_field,
> > > kcu.ordinal_position
> > >
> > > Those are the needed columns, as the end resultset.
> >
> > But that is different then what you are asking from the MySQL query:
> >
> > http://dev.mysql.com/doc/refman/5.7/en/information-schema.html
> >
> > There is no constraint_type in the information_schema tables you
> > reference in the MySQL query, unless I am missing something.
> >
> > >
> > > On Fri, 26 Feb 2016 at 10:51 Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
> > >
> > > On 02/25/2016 04:38 PM, David Binney wrote:
> > > > Hey guys,
> > > >
> > > > I am having a tricky problem which I have not needed to
> > solve before.
> > > > Basically one of the php frameworks I am using needs to
> > get the same
> > > > dataset from mysql and postgres but I am not sure how to
> > do the
> > > joins.
> > > >
> > > > Below i have the mysql version of the query which work ok,
> > and after
> > > > that i have my attempt at the postgresql version, which is
> > not joined
> > > > correctly. Any help would be greatly appreciated, and in
> the
> > > meantime i
> > > > will keep guessing which columns need to be joined for
> > those three
> > > > tables, but I am thinking there could be a view or
> > something to
> > > solve my
> > > > problem straight away??
> > >
> > > The * in your MySQL query hides what it is you are trying
> > retrieve.
> > >
> > > So what information are you after?
> > >
> > > Or to put it another way, what fails in the Postgres version?
> > >
> > > >
> > > > -------mysql working version----------
> > > > SELECT
> > > > *
> > > > FROM
> > > > information_schema.key_column_usage AS kcu
> > > > INNER JOIN information_schema.referential_constraints AS
> > rc ON (
> > > > kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
> > > > AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
> > > > )
> > > > WHERE
> > > > kcu.TABLE_SCHEMA = 'timetable'
> > > > AND kcu.TABLE_NAME = 'issues'
> > > > AND rc.TABLE_NAME = 'issues'
> > > >
> > > > ---- postgresql partial working version--------------
> > > >
> > > > select
> > > > rc.constraint_name AS name,
> > > > tc.constraint_type AS type,
> > > > kcu.column_name,
> > > > rc.match_option AS match_type,
> > > > rc.update_rule AS on_update,
> > > > rc.delete_rule AS on_delete,
> > > > kcu.table_name AS references_table,
> > > > kcu.column_name AS references_field,
> > > > kcu.ordinal_position
> > > > FROM
> > > > (select distinct * from
> > > information_schema.referential_constraints) rc
> > > > JOIN information_schema.key_column_usage kcu
> > > > ON kcu.constraint_name = rc.constraint_name
> > > > AND kcu.constraint_schema = rc.constraint_schema
> > > > JOIN information_schema.table_constraints tc ON
> > > tc.constraint_name =
> > > > rc.constraint_name
> > > > AND tc.constraint_schema = rc.constraint_schema
> > > > AND tc.constraint_name = rc.constraint_name
> > > > AND tc.table_schema = rc.constraint_schema
> > > > WHERE
> > > > kcu.table_name = 'issues'
> > > > AND rc.constraint_schema = 'public'
> > > > AND tc.constraint_type = 'FOREIGN KEY'
> > > > ORDER BY
> > > > rc.constraint_name,
> > > > cu.ordinal_position;
> > > >
> > > > --
> > > > Cheers David Binney
> > >
> > >
> > > --
> > > Adrian Klaver
> > > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com
> >>
> > >
> > > --
> > > Cheers David Binney
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
> > --
> > Cheers David Binney
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
--
Cheers David Binney