我可以将ActiveRecord关系与Hstore中的字段一起使用吗？ [英] Can I use ActiveRecord relationships with fields from an Hstore?

问题描述

我可以使用hstore哈希中的字段通过活动记录belongs_to将模型绑定到另一个模型吗？我将详细说明：

Can I tie a model to another through active record belongs_to using a field from an hstore hash? I'll elaborate:

我有一个用户模型，该模型通过STI在其字段中的一个子类中根据权限被子类化为许多其他用户模型：

I have a User model that gets subclassed via STI on one of its fields to many different other User models based on permissions:

class User < ActiveRecord::Base
  self.inheritance_column = :role
  #other definitions and validations
end

这是一个这样的子模型，nightclub_boss模型，适用于我的应用程序的管理用户：

Here's one such submodel, the nightclub_boss model, meant for administrative users for my app:

class NightclubBoss < User
  belongs_to :nightclub     #the boss record has a nightclub_id
  has_one :rp_boss          #rp_boss should have a nightclub_boss_id
  has_one :captain          #captain should have a nightclub_boss_id
end

此处的想法是使用以下信息扩展用户模型：

The idea here is to expand the User model with info such as:

-User hierarchy (which user reports to who; get a list of who is under who as needed through the ORM)
-User origin (which user belongs to whatever other models are in the app)

所以我想出了什么，以避免用户表稀疏并且为了避免制作大量局部的小SQL表，是在User模型上创建一个称为层次结构的hstore字段，并将其映射到User模型上的不同属性，该属性应在仅当且仅当有问题的用户需要存储层次结构信息（即基于角色的基础； nightclub_boss不应具有相同的信息）层次结构信息以及我的应用程序中的其他模型）：

So what I came up with, to avoid having a sparse User table and also to avoid making lots of partial little SQL tables, is to create an hstore field on the User model called "hierarchy" and map it to different attributes on the User model which should get populated if and only if the User in question needs to store hierarchy information (that is, on a role-dependent-basis; nightclub_boss should not have the same hierarchy information as other models in my app):

class AddHstore < ActiveRecord::Migration
  def self.up
    enable_extension "hstore"
  end

  def self.down
    disable_extension "hstore"
  end
end

class AddHierarchyToUser < ActiveRecord::Migration
  def change
    add_column :users, :hierarchy, :hstore
    add_index :users, :hierarchy, using: :gin
  end
end

Ran

rake db:migrate

然后我在模型中添加了hstore_accesssor：

And then I added hstore_accesssor to my model:

Gemfile：

gem "hstore_accessor"

用户模型：

class User < ActiveRecord::Base
  self.inheritance_column = :role
  hstore_accessor :hierarchy, nightclub_id: :integer
  #other validations and definitions...
end

到目前为止，一切都很好，使用硬编码数据进行了测试：

Everything is good so far, testing with hardcoded data:

[1] pry(main)> NightclubBoss.find(99)
  NightclubBoss Load (1.3ms)  SELECT  "users".* FROM "users" WHERE "users"."role" IN ('NightclubBoss') AND "users"."id" = $1 LIMIT 1  [["id", 99]]
=> #<NightclubBoss:0x000000070bbb00
 id: 99,
 #...all other fields...
 role: "NightclubBoss",
 hierarchy: {"nightclub_id"=>"1"}>

[2] pry(main)> NightclubBoss.find(99).nightclub_id
  NightclubBoss Load (0.7ms)  SELECT  "users".* FROM "users" WHERE "users"."role" IN ('NightclubBoss') AND "users"."id" = $1 LIMIT 1  [["id", 99]]
=> 1

因此，您希望滑轨拉动与此绑定的相应Nightclub.find（1）叫 NightclubBoss.nightclub的模特，对吗？嗯，这不会发生：

So you'd expect rails to pull the corresponding Nightclub.find(1) that's bound to this model when calling "NightclubBoss.nightclub", right? Well, it doesn't happen:

[3] pry(main)> NightclubBoss.find(99).nightclub
  NightclubBoss Load (0.7ms)  SELECT  "users".* FROM "users" WHERE "users"."role" IN ('NightclubBoss') AND "users"."id" = $1 LIMIT 1  [["id", 99]]
=> nil

我已经尝试过各种方法来弄清楚这一点，但我没有找到

I've tried all sorts of things to try to figure this out and I haven't found the answer yet, is there anyone who can help?

作为解决方法，我意识到我可以做到：

As a workaround I realize I can do:

Nightclub.find(NightclubBoss.find(99).nightclub_id)

我得到的查询就很好了。但是我认为这可以改善，您希望能够做到 NightclubBoss.find（99）.nightclub

And I get the query just fine. But I think this can be improved, you'd expect to be able to do NightclubBoss.find(99).nightclub

我认为这可能也是一个非常糟糕的做法。如果有更好的方法来建模所需信息的类型，请告诉我，我们将非常感谢您的建议。谢谢！

I think this may also be a very bad practice to follow. If there's a better way to model the kind of information that I need, please let me know, I'll appreciate your advice. Thanks!

推荐答案

一夜之间，我发现Postgres有一个类似的数据类型称为Jsonb，它也可以在

I found overnight that Postgres has a similar data type called Jsonb, which also does a similar thing to hstore in the sense that you store a hash in a field in the relational database.

显然，由于ActiveRecord的限制，目前无法实现将匹配限制为关系的匹配仅限数据库字段：

Apparently it is not currently possible due to a limitation in ActiveRecord to do this match that constrains matches to relational database fields only:

ActiveRecord中的PostgreSQL jsonb字段属居关联

我个人对此感到满意，因此如果没有更多信息，我将关闭该线程，当然，我会修改自己的架构。

Personally, I'm satisfied with that answer so I'll close this thread if nobody has more info, and of course I'll modify my schema.

想知道是否可以修补支持吗？

Wonder if support could be patched?

这篇关于我可以将ActiveRecord关系与Hstore中的字段一起使用吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！