Join multiple tables rails sql query

Question

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'.

In Key object I keep information about type of Metadatum.

Metadatum table:

id
key_id
account_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'
Metadatum with value = 'John' that belongs to Key with name = '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.

What would be the right query?

Pablo · Accepted Answer · 2018-04-07 01:15:15Z

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])

Below is the sql query that I have. Still does not show me accounts that has both metadata
SELECT "accounts".* FROM "accounts" INNER JOIN "metadata" ON "metadata"."account_id" = "accounts"."id" AND "metadata"."deleted_at" IS NULL INNER JOIN "keys" ON "keys"."id" = "metadata"."key_id" AND "keys"."deleted_at" IS NULL WHERE "accounts"."deleted_at" IS NULL AND (keys.name = 'first_name' AND metadata.value = 'Robert') AND (keys.name = 'age' AND metadata.value = '18')
I tried a second approach. I don't like it, but it might work.

Salil · Accepted Answer · 2018-04-06 17:00:25Z

0

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])

answered Apr 6, 2018 at 17:00

Salil

47.7k22 gold badges127 silver badges161 bronze badges

3 Comments

elmuzyk Over a year ago

Thanks but this is not what I want, because If I search for "John"(metadata1) that is "18"(metadata2) years old I will also receive all NOT"Johns" that are "18" and all "Johns" that are NOT"18"

Salil Over a year ago

Nope in that case your query should be only accounts.joins(metadata: :key).where("(keys.name = ? AND metadata.value = ?)", params[:key], params[:value])

Salil Over a year ago

You have to generate dynamic queries depending on the parameters

Collectives™ on Stack Overflow

Join multiple tables rails sql query

2 Answers 2

3 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Related