embedding returning unrelated results w/ foreign key field filter applied

[dweinstein]

create schema if not exists public;

create table if not exists public.apps (
  id serial PRIMARY KEY,
  name text
);

create table if not exists public.issues (
  id serial PRIMARY KEY,
  app_id int NOT NULL REFERENCES public.apps (id),
  data text
);

insert into public.apps (name) values ('foobar');
insert into public.apps (name) values ('foobaz');
insert into public.issues (app_id, data) values (1, 'blah');
insert into public.issues (app_id, data) values (1, 'thingy');
insert into public.issues (app_id, data) values (2, 'pass');

tested w/ docker image w/ tag fd8db3203b33 ( PostgREST 0.3.2.0)

request: http://localhost:3000/issues?select=*,apps{*}&apps.name=eq.foobar

[
  {
    "id": 1,
    "app_id": 1,
    "data": "blah",
    "apps": {
      "id": 1,
      "name": "foobar"
    }
  },
  {
    "id": 2,
    "app_id": 1,
    "data": "thingy",
    "apps": {
      "id": 1,
      "name": "foobar"
    }
  },
  {
    "id": 3,
    "app_id": 2,
    "data": "pass",
    "apps": null
  }
]

expected: first two but not last entry

cc @ruslantalpa

[ruslantalpa]

Everything is correct.
Your query means:
give me all the issues and for all of them, embed the apps but only if it meats the criteria name = ...
it does NOT mean:
give me all the issues for which the app name is ....

in more general terms, conditions like subtable.column=eq.value have no influence on the upper/top level, they are applied only on the level of subtable

[ruslantalpa]

(please close)

[dweinstein]

ok, is there a way that would work the way I was envisioning? Closing now.

[ruslantalpa]

without a view no

[brian-leidos]

FWIW, this can be accomplished (as noted in your Advanced Endpoints wiki article) by using functions.

CREATE FUNCTION package_name(issues) RETURNS TEXT AS $$
SELECT name FROM apps WHERE id = $1.app_id
$$ STABLE LANGUAGE SQL;

Then you can:
http://localhost:3000/issues?select=*,apps{*}&name=eq.foobar

[ruslantalpa]

@brian-leidos i guess it's possible to have that function that basically creates a virtual column in issues table with data from the apps table, and you filter based on it. This can work if the returned dataset is not big (less then 100) but i think it will be much less optimal then a view (that function will be executed for each issue)