I have a table on postgresql database in this structure:
Code:
-- table structure --
create table public.blocks
(
id_quadrant integer default 0 not null
constraint quadrant_id_quadrant
references public.quadrant
on delete cascade,
id_neighborhoods integer default 0 not null
constraint neighborhoods_id_neighborhoods
references public.neighborhoods
on delete cascade,
id_aggr_block integer default 0 not null
constraint aggr_block_id_aggr_block
references public.aggr_block
on delete cascade,
id_blocks serial not null
constraint blocks_pk
primary key,
the_geom geometry(MultiPolygon, 32638),
layer varchar(254),
text varchar(254),
protection varchar(50),
attributes varchar(254),
zone varchar(50),
surveyed_1 varchar(50),
area double precision,
density double precision,
data_house varchar(3),
created_at timestamp,
updated_at timestamp,
deleted_at timestamp
);
-- comment --
comment on table public.blocks is 'Blocks';
-- owner --
alter table public.blocks
owner to postgres;
-- index --
create unique index blocks_id_blocks_uindex
on public.blocks (id_blocks);
I would like to know the foreign key data of the table
Using the getForeignKeyData () function invokes the _foreignKeyData function in the Connection.php file, which uses this query:
Code:
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='blocks';
As a result, I get 50 rows back, which is incorrect because there are only 3 foreign keys
The correct result would be obtained if the query were a SELECT DISTINCT.
In your opinion, is it more correct to modify the query with SELECT DISTINCT or to filter array of objects?
In what cases might the query without SELECT DISTINCT make sense?
Thanks for the replies
