加入多个表rails sql查询

class Account
 has_many :metadata

class Keys
 has_many :metadata

class Metadatum
 belongs_to :key
 belongs_ to :account

In Metadatum object I keep additional information about Account, for exapmle 'age'.

在Metadatum对象中，我保留有关帐户的其他信息，以获取exapmle'age'。

In Key object I keep information about type of Metadatum.

在Key对象中，我保留有关Metadatum类型的信息。

Metadatum table:

Metadatum表：

id
ID
key_id
KEY_ID
account_id
帐户ID
value
值

Key table:

关键表：

name
名称
data_type
数据类型

I want to search accounts by multiple metadata. For example

我想通过多个元数据搜索帐户。例如

Metadatum with value = '18' that belongs to Key with name = 'age'
值为'18的Metadatum属于名为='age'的Key
Metadatum with value = 'John' that belongs to Key with name = 'first_name'
Metadatum的值='John'属于Key，名称='first_name'

My query is:

我的查询是：

    accounts.joins(metadata: :key).where("keys.name = ? AND 
    metadata.value = ?", params[:key], params[:value]).where("keys.name 
    = ? AND metadata.value = ?", params[:key1], params[:value1])

It is wrong because in my opinion it looks for an Account that has Metadatum with both key_ids and values. No such Metadatum exists - each has only one key_id and value.

这是错误的，因为在我看来，它会查找具有key_id和值的Metadatum帐户。没有这样的Metadatum - 每个只有一个key_id和值。

What would be the right query?

什么是正确的查询？

2 个解决方案

#1

If you want accounts that comply with both conditions, I would try:

如果您想要符合这两个条件的帐户，我会尝试：

first = Account.joins(metadata: :key).where("keys.name = ? AND metadata.value = ?", params[:key], params[:value])

final = first.joins(metadata : :key).where("keys.name 
= ? AND metadata.value = ?", params[:key1], params[:value1])

New try (really ugly :))

新尝试（真的很丑:)）

first_ids = Account.joins(metadata: :key).where("keys.name = ? AND metadata.value = ?", params[:key], params[:value]).pluck(“accounts.id”)

final = Account.where(id: first_ids).joins(metadata : :key).where("keys.name = ? AND metadata.value = ?", params[:key1], params[:value1])

#2

Try following

试试以下

accounts.joins(metadata: :key)
        .where("(keys.name = ? AND metadata.value = ?) OR 
                (keys.name = ? AND metadata.value = ?)",  
         params[:key], params[:value], params[:key1], params[:value1])

#1