How to joing join multiple repositories with Ecto?

Questions / Help
,
1

I have two databases, the main databases (PostgreSQL) + the statistics database (ClickHouse). Statistics database contains a subpart of data from the main database which is enough for performing calculations. All ids are similar ( :binary_id ) across both databases. I need to find a way of joining the results obtained from the statistic database with a query to the main database. In terms of pure SQL solution it could be something like this, where VALUES are data obtained from statistics database:

SELECT p0."id",
       p0."name",
       f1."average_count"
FROM "persons" AS p0
         JOIN (VALUES (0.0, '906af2c0-cde2-4996-9a98-bdbf986fe687'::uuid),
                      (0.2857142857142857, 'aba7c694-3453-4a55-aab9-4b542dbb4ba9'::uuid),
                      (0.2857142857142857, '2dab3350-6149-4752-a55e-7477a6ad0dd3'::uuid))
               as f1 (average_count, user_id)
              on f1.user_id = p0.id;

My project actively uses Ecto and has a lot of on-the-fly constructed queries. That’s why I cannot just perform pure SQL queries as I post above and should have Ecto based solution. Is there a way to do such a joining with Ecto?

2

Just in case someone will look for an answer: https://stackoverflow.com/questions/60291923/how-to-joing-join-multiple-repositories-with-ecto/60311589#60311589

1 Like
3

Even more efficiently if there is a FDW for the other database then you could have postgresql access it directly then you’d literally be able to join across them and all in a single sql query.

EDIT: Yes, it does look like there is a FDW for Clickhouse:

2 Likes