How to map a joint with crystal-db?

Help & Support
1

Hello,

I am looking for a way to map the result of a join (with crystal-db). Using DB::Serializable or even another format I will map in post processing.

Example of what I am trying to do:

db.query_all(
  "select users.* as user, posts.* as posts from users left join posts on posts.user_id = users.id", 
  as: {user: User, posts: Post?}
)

or maybe something like this:

db.query_all(
  "select users.*, posts.* from users left join posts on posts.user_id = users.id", 
  as: {User, Post?}
)

But doesn’t work.

Note: User and Post include DB::Serializable

The idea is to get the result like:

  • one instance user.posts # => Array(Post)
  • or two user # => User and posts # => Array(Post)`
  • even a Tuple (or NamedTuple) to do a post processing

if you know an example or a solution, it would be very useful.

2

DB::Serializable is very basic, it just maps a single table. No join support. In the future I would like to deprecate that type.

Pleause use an ORM for this.

3

I have a proof of concept that makes a query for multiple DB::Serializable models work. However, it must be an INNER JOIN.

The hard part about doing any kind of OUTER JOIN is that you can’t tell by looking at a single column whether or not that model’s segment of the result row should be nil or if only that individual property would be. And, unfortunately, DB::Serializable reads an individual column at a time from the ResultSet.

In order to be able to process the results of an outer join, I have a feeling it would have to read all the columns for that particular model before assigning them to instance variables and then, if they’re all nil, return nil instead of an initialized model. But then, that gets weird if nil is a valid value for all of the properties of that model.

This is one of my least favorite things about SQL, though — rows being a flat representation of multiple domain entities when joined with no clear delineation between them is irritating. I know why it’s like that, but I still don’t like it. :joy:

1 Like
4

@asterite it’s noted, thanks. Just for info, if DB::Serialize will be deprecated, do you envisage an alternative?
Even if it doesn’t go through DB::Serialize, being able to get the result grouped by example in user and posts (Tuple or NamedTuple or Hash, … or whatever) would be very nice (it’s a solution).

@jgaskins Thanks for the help. Indeed, not simple. I will test your solution and continue to investigate.

5

No. DB::Serliazable was just an experiment. For some reason it ended up there instead of being removed. Serializing DB rows isn’t as simple as with JSON or YAML, where the structure is mostly the same for a given resource.

The correct way to do it is by using an ORM like Lucky’s avram. Then you can specify joins, eager or lazy loading, etc.

6

Author of DB::Serializable here, I created it simply because there was already a DB.mapping macro that I wanted to integrate with the JSON/YAML::Serializable systems that had just been created at the time.

1 Like
7

Ok for DB::Serializable, thanks. I forget this way.

@asterite I have tested some Crystal ORMs (Granite, Jennifer, Crecto, …). Like jgaskins, I’m just looking for a way to get the result of a join (from the ResultSet). Of course, It’s ok by listing all types, something like that:

# tedious for a join
query_all(sql, args: args, as: {id: Int64, username: String, ...})

Here, considering that UserType is a NamedTuple or Tuple or other:

db.query_all(
  "select users.* as user, posts.* as posts from users left join posts on posts.user_id = users.id", 
  as: {user: UserType, posts: [PostType]?}
)

But this does not work, nothing that indicates the user part (users.* as user) and the posts part (posts.* as posts) of the query. Unless I’m wrong, the ResultSet result is flat (one dimension).

Is there a way to get the result grouped in user and posts?

8

Assuming those ORM’s are like ActiveRecord, you might need to alias any fields that are in both models (e.g.: users.id as u_id, posts.id as p_id).

1 Like
9

Unfortunately, there is not. Right now, DB::Serializable tries to deserialize the entire row into the model you supply. I think that’s why Ary mentioned you should use an ORM.

I’m not a huge fan of the Crystal ORMs I’ve tried, though, which is why I tend to stick to DB::Serializable when I can. I’m currently working on an ORM that implements what I’d like to see in one (separating the persisted objects from their persistence, encapsulating the DB schema from the application, etc). It’s currently based on DB::Serializable, but if it’s going away I should probably change that. :slightly_smiling_face:

1 Like
10

@jgaskins Ditto, I use a homemade ORM, I’m very happy with it. Except to type the return of the join result, it’s tedious and time consuming.

@drhuffman12 well, yes. It seems to me to be the way to go. Next step, find a not too intrusive strategy to map the fields (a macro that prefixes the fields, I guess).

Thanks for the help :+1:

1 Like