![]() ![]() Which then goes all back up and appends all these sources onto the main sources list. That is how it is populating the schema as it is, probably is that whatever schema the initial ‘from’ is becomes the schema for all the joins, even when they have their own, specifically the joins are ignoring the on all schemas in the join and overriding it with the from’s Since I am using the from syntax, but it fails on joins, I’m curious if there’s been any movement towards fixing this long-standing bug or if there is any safe work-around that does not involve dropping into raw SQL (or fragments)? I’m entirely halted on integrating these new tables until I drop down to raw SQL statements, which is not as careful for obvious reasons. So yes, it does know, it has the information. In PostgreSQL, the prefix is called “SCHEMA” (typically set via Postgres’ search_path). If a schema is used as a join or part of an assoc, won’t be obeyed. When set, the prefix will be used by every built struct and on queries where the current schema is used in from (and only from exclusively). Defaults to nil, which generates structs and queries without prefix. Specifically (bolding is my own - configures the schema prefix. This has suddenly put a right-on stop on my work because of this bug… >.< Which of course works fine from the database directly. When the SQL should of course be: SELECT s0.* FROM "otherschema"."s" AS s0 INNER JOIN "myschema"."tags" AS t1 ON t1."name" = s0."last_name" WHERE (s0."id" = 1) Which is of course entirely a wtf and causes it to puke with: ** (Postgrex.Error) ERROR 42P01 (undefined_table): relation "otherschema.tags" does not exist Is giving a query like (abbreviated): SELECT s0.* FROM "otherschema"."s" AS s0 INNER JOIN "otherschema"."tags" AS t1 ON t1."name" = s0."last_name" WHERE (s0."id" = 1) Right now doing something like: Repo.one(from(s in RemoteDB.S, join: t in Tag, on: t.name=s.last_name, where: s.id=1)) I just updated Ecto and it still has this bug. ![]() I know Ecto did have this bug last year, does it have it fixed ‘now’ though? Right now the schema_prefix definition on ecto schema seems entirely ignored and it is instead using the wrong schema on the join, still. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.īut when I pass just an integer (and not a list), it works correctly.So I got handed more tables in a different schema from mine that I need to cross-join on. |> join(:inner,, c in "cat_tree", on: c.id = b.category_id)īut when I pass (for example) to the function, it gives me the following error: (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808.9223372036854775807, got '\f\r'. |> with_cte("cat_tree", as: ^category_ids)) Join cat_tree as parent on parent.id = child.parent_idĭef category_search_query(query, ), do: queryĭef category_search_query(query, category_ids) do Here's the query: with recursive cat_tree as ( But it would send several queries to the database and I want to do it in one query. I could use that to fetch all of the subcategories of category_ids categories and use the new list. I've already written a query that returns all of a given category's children. I want a function that takes a list of category ids ( category_ids) and returns a query that gets books which belong to one of the given categories or their children (recursive). I have a categories table with the following columns: idĪnd a books table which has a category_id column inside of it. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |