How to add relations to a View collection.

[Xyphyn]

I have a view collection, and I want it so that I can query the amount of members and posts within a community. Here's what I've got:

SELECT
    communities.id,
    count(DISTINCT posts.id) as posts,
    count(DISTINCT users.id) as members
FROM communities
LEFT JOIN users on users.communities LIKE '%' || communities.id || '%'
LEFT JOIN posts on posts.community = communities.id
GROUP BY communities.id

This works, but it only has an id- it's not a relation, meaning I can't indirect expand. How do I make it so that there's a column community that is a relation to 1 community?

[ganigeorgiev]

You can add the column communities.id as community but in order to be detected as relation it needs to be from "non-primary" table (I'll consider updating the rules and allow any aliased id column to be detected as relation).

As a workaround, for now you can try specifying a dummy primary table:

SELECT
    communities.id,
    communities.id as community,
    count(DISTINCT posts.id) as posts,
    count(DISTINCT users.id) as members
FROM (select 1) as dummy, communities
LEFT JOIN users on users.communities LIKE '%' || communities.id || '%'
LEFT JOIN posts on posts.community = communities.id
GROUP BY communities.id

[pyronaur]

Thank you, this helped me set up a view table. Why does it need to be non-primary?

[ganigeorgiev]

@pyronaur Please note that the above dummy table workaround is no longer necessary and any id referenced column (excluding the id of the view itself) will be automatically mapped to a relation field.

[pyronaur]

Oh, which version was that added in? I'm using 0.13 still, probably time to upgrade.

[ganigeorgiev]

@pyronaur It was added in v0.14.0. Make sure to have a backup of your pb_data before upgrading! You can try upgrading directly to the latest v0.15.1 release. There are no breaking changes to the public web APIs; you can find a summary of the changes in https://github.com/pocketbase/pocketbase/blob/master/CHANGELOG.md.

For the next v0.16/0.17 releases I'm planning a self-update UI (at least for the prebuilt executable) which should make the updates a little less cumbersome.

[pyronaur]

Thanks, @ganigeorgiev - and thank you for all the hard work on pocket-base - it's amazing. Been using it for a couple of months now and I love it!

[irishburlybear]

This little post helped me so here is my code for others to use: (It's just to count how many times a certain id is used in another table)
SELECT c.id, c.name, ( SELECT COUNT(*) FROM cases WHERE cases.contaminants LIKE '%' || c.id || '%' ) as counter FROM (select 1) as dummy, contaminants AS c

[M4rk3tt0]

Hi, I have some issues too finding a way to add a relation to a view collection.

This is a simplified use case. I have a collection that has three fields:

• id
• inventory: that is a json field and it contains an array of strings
• store: that contains the id of the stores table (it's a relation in pocketbase)

CREATE TABLE containers (id TEXT, inventory JSON, store TEXT);

These are some example records:

INSERT INTO containers (id, inventory, store) VALUES
  ('1', '["Box","Car"]','store_id_1'),
  ('2', '["Box", "Apple"]', 'store_id_1'),
  ('3', '["Apple","House"]', 'store_id_2');

Now what I want to achieve is to have a view collection that for each store provides an array with unique inventory items.
I used this code and it works as expected.

SELECT 
  (ROW_NUMBER() OVER()) as id,
  json_group_array(value) as items, 
  store
FROM (SELECT DISTINCT value, store FROM containers, json_each(inventory))
GROUP BY store

id	items	store
1	["Box","Car","Apple"]	store_id_1
2	["Apple","House"]	store_id_2

The problem is that the store column has no relation with the stores table

I even tried referencing an id column like this but with no luck...

SELECT 
  (ROW_NUMBER() OVER()) as id,
  stores.items,
  stores.id as store
FROM (SELECT 
  json_group_array(value) as items, 
  store as id
FROM (SELECT DISTINCT value, store FROM containers, json_each(inventory))
GROUP BY store) as stores

How can this be achieved?

[ganigeorgiev]

@M4rk3tt0 Is containers a collection or just a plain table?

The store field will be detected as relation only if there is a collection containers with relation field store.

[ganigeorgiev]

On second read, it is the subselect expression in the FROM clause that prevents correctly mapping the store field as relation since it is treated as an expression, aka. we don't operate directly with the containers table.

If you want store to be detected as relation you have to remove the subselect, or add another FROM/JOIN statement with just the plain containers table.

[ganigeorgiev]

An example query could be:

SELECT 
    (ROW_NUMBER() OVER()) as id,
    c.store,
    json_group_array(DISTINCT je.value) as items
FROM containers c
LEFT JOIN json_each(c.inventory) as je
GROUP BY c.store

[M4rk3tt0]

Thank you! It works perfeclty!

[Khalilsqu]

SELECT
    u.id,
    u.email,
    u.name,
    COALESCE(a.college, s.college) AS college
FROM users u
LEFT JOIN advisor a ON u.email = a.email
LEFT JOIN student s ON u.email = s.email;

i have a college collection which is a relation field to both the student and advisor collections. in the newly created view collection, i receive the college as JSON {}. relation field is lost! users is the default auth collection.

[ganigeorgiev]

This is expected. At the moment expressions (in your case the COALESCE(...)) are always resolved as json unless there is a cast to text, number or bool.

If you want to preserve the a.college and s.college relation field type you'll have to add them as separate columns.

Enforcing custom collection schema is in the roadmap and eventually will be added in the future.

[ganigeorgiev]

Another possible solution could be to join the college collection and add the matching record id in the select. Something like:

SELECT
    u.id,
    u.email,
    u.name,
    c.id as college
FROM users u
LEFT JOIN advisor a ON u.email = a.email
LEFT JOIN student s ON u.email = s.email
LEFT JOIN college c ON (c.id = a.college OR c.id = s.college)

[Khalilsqu]

Another possible solution could be to join the college collection and add the matching record id in the select. Something like:

SELECT
    u.id,
    u.email,
    u.name,
    c.id as college
FROM users u
LEFT JOIN advisor a ON u.email = a.email
LEFT JOIN student s ON u.email = s.email
LEFT JOIN college c ON (c.id = a.college OR c.id = s.college)

thank you very much, much appreciated. thanks. works perfectly.