Cross joins not working in view collections

[TheEaterr]

Cross joins not working in view collections

[ganigeorgiev]

It is not the JOIN that is failing but the duplicated id field (rank.id and daily.id will result in 2 fields with the same name - id).

The view validation errors will be improved in the future, but for now you can try specifying an alias (eg. daily.id as dailyId).

If that doesn't help, feel free to let me know I'll to investigate it tomorrow.

[TheEaterr]

Wow what a fast answer ! Yes that does solve the format problem but the other one remains, the cross join isn't being made I only get each row of the first collection (with additional columns for the first row of the second one).

In any case, I was able to find a solution to my use case using nested selects. Thank you very much!

[ganigeorgiev]

but the other one remains, the cross join isn't being made I only get each row of the first collection

This will depend on your query and data structure. The easiest way to rule out PocketBase issue is to run your query against the pb_data/data.db file using the sqlite3 cli:

sqlite3 ./pb_data/data.db 'your query'

If you have other questions please open a new Q&A discussions as the threaded format is more suitable for this.

[TheEaterr]

It works fine using the sqlite cli, you can try anything using a cross join and the bug will appear.
SELECT table1.id, table2.id as other_id FROM table1 CROSS JOIN table 2

[ganigeorgiev]

I'm not able to reproduce it. I've just tested it locally and both produce the same result.

Once the query is saved it is executed as plain View by the specific SQLite driver (if CGO is enabled mattn/go-sqlite3, otherwise modernc.org/sqlite).

Additionally I don't understand what is the purpose of the cross join in your case especially when there is no explicit ON constraint.

I don't think I can help you further but if you have further questions please open a Q&A discussion with more details about your use case, what you are trying to do and an export of your collections configuration (Admin UI > Settings > Export collections).