Comments (6)
@sam-huckaby which version are you using?
Trying the code you provided on my machine (with petrol 1.2.0) I get the same behaviour as my example (an alias to join_tmp_0
) and the provided work-around works.
Since this ticket is still open, you have to assume this is the recommended way to do join queries for now 🙂
from petrol.
Ah, no @beajeanm, that is a bug.
I introduced these anonnymous aliases as a quick fix because Postgres complains if all subqueries aren't named, but it seems the change broke queries that should otherwise work.
You can avoid the problem my using Expr.as_ ~name expr
to rename your expressions:
let owner_id, owner_id_ref = Expr.(as_ owner_id ~name:"owner_id") in
let pet_name, pet_name_ref = Expr.(as_ pet_name ~name:"pet_name") in
Query.select ~from:person Expr.[id; name; pet_name_ref]
|> Query.join ~on:Expr.(id = owner_id_ref) (Query.select ~from:pet Expr.[owner_id; pet_name])
|> Format.asprintf "%a" Query.pp;;
but evidently, I think it would be better to have a proper implementation of aliasing for tables that takes this into account.
I'll implement this today or tomorrow.
from petrol.
Thanks, I'll have a try at that
from petrol.
I'm wondering if this bug was addressed yet? I'm having sort of the reverse problem, where my subqueries are not being aliased at all and that causes my joins to fail.
I'm using the below table definitions and decoder function.
(* declare a table, returning the table name and fields *)
module Users = struct
let table, Expr.[id ; username ; bio ; display_name] =
VersionedSchema.declare_table schema ~name:"users"
Schema.[
field ~constraints:[primary_key ~auto_increment:true ()] "id" ~ty:Type.int;
field "username" ~ty:Type.(character_varying 32);
field "bio" ~ty:Type.(character_varying 32);
field "display_name" ~ty:Type.(character_varying 32);
]
end
(* declare a table, returning the table name and fields *)
module Posts = struct
let table, Expr.[id ; user_id ; message ; created] =
VersionedSchema.declare_table schema ~name:"posts"
Schema.[
field ~constraints:[primary_key ~auto_increment:true () ; not_null ()] "id" ~ty:Type.big_int;
field ~constraints:[foreign_key ~table:Users.table ~columns:Expr.[Users.id] ()] "user_id" ~ty:Type.int ;
field "message" ~ty:(Type.character_varying 140) ;
field "created" ~ty:Type.time ;
]
end
type post_result = {
id : int64 ;
message : string ;
username : string ;
display_name : string ;
created : Ptime.t ;
}
let decode
(id,
(message,
(username,
(display_name,
(created, ()))))) = {
id = id ;
message ;
username ;
display_name ;
created ;
}
let fetch_posts db =
let users = Query.select [Users.id ; Users.username ; Users.display_name] ~from:Users.table in
let posts = Query.select [Posts.id ; Posts.message ; Users.username ; Users.display_name ; Posts.created] ~from:Posts.table in
let on = Expr.(Users.id = Posts.user_id) in
Query.join ~op:INNER ~on users posts
|> Request.make_many
|> Petrol.collect_list db
|> Lwt_result.map (List.map decode)
The fetch_posts function generates SQL which looks like the following:
SELECT
posts.id,
posts.message,
users.username,
users.display_name,
posts.created
FROM posts
INNER JOIN (
SELECT
users.id,
users.username,
users.display_name
FROM users
)
ON (users.id) = (posts.user_id)
Which lacks an alias for the subquery generated for the inner join. If you point me in the right direction, I could try to work on a fix. I really appreciate your work on this library, it's made OCaml a lot of fun to learn.
from petrol.
Interesting! I am also using 1.2.0, but I'm not getting an alias (or at least the error I'm getting back from PostgreSQL indicates that it is missing. I think this question would be better suited to a separate issue though, since I originally misunderstood the workaround being suggested here. sorry to clutter things up.
from petrol.
or at least the error I'm getting back from PostgreSQL indicates that it is missing.
That why in the example above we've printed the produce SQL rather than trying to understand what went wrong based on the DB error message.
since I originally misunderstood the workaround being suggested here.
The current version use the pattern Table.field
in the select clause, but Table
is only mentioned in the sub-query of the join, so it's not a valid identifier in the top level.
The current solution is to manually alias the fields used in the subquery so they can be re-used in the select clause.
So
let owner_id, owner_id_ref = Expr.(as_ owner_id ~name:"owner_id") in
let pet_name, pet_name_ref = Expr.(as_ pet_name ~name:"pet_name") in
Query.select ~from:person Expr.[id; name; pet_name_ref]
|> Query.join ~on:Expr.(id = owner_id_ref) (Query.select ~from:pet Expr.[owner_id; pet_name])
|> Format.asprintf "%a" Query.pp;;
Will geneate the following SQL.
SELECT person.id, person.name, pet_name
FROM person INNER JOIN (SELECT pet.owner AS owner_id, pet.name AS pet_name
FROM pet) AS join_tmp_0 ON person.id = owner_id
As you can see when you compare that to the first SQL generated, each field in the sub select is aliased (SELECT pet.owner AS owner_id, pet.name AS pet_name
). So these aliases become valid fields for the top level query.
from petrol.
Related Issues (7)
- Support for additional Caqti_types HOT 5
- Update opam package HOT 1
- Isolate Lwt/Async stuff HOT 4
- Complete example? HOT 1
- Consider dual license HOT 4
- sqlite3: `expected BOOLEAN got BOOLEAN` HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from petrol.