static static - 1 year ago 44
SQL Question

How to find referenced documents in a collection (multilevel query / join) in mongodb using mongoid in rails app?

It is all about

has_and_belongs_to_many
relation and queries in
mongodb
using
mongid3
in rails application.

I have
class A
and
class B
, say:

class A
include Mongoid::Document
has_and_belongs_to_many :bs
end


and

class B
include Mongoid::Document
has_and_belongs_to_many :as
end


Now instantiate 3
a
-s and 3
b
-s:

a1 = A.create()
a2 = A.create()
a3 = A.create()

b1 = B.create()
b2 = B.create()
b3 = B.create()


Now reference them one in another, e.g:

a1.bs.push([b1, b3])
a2.bs.push([b2, b3])
a3.bs.push(b2)


So:

b1.as.all
=> [<#A _id: 1000000000000001>] # i.e. a1

b2.as.all
=> [<#A _id: 1000000000000002>, <#A _id: 1000000000000003>] # i.e. a2, a3

b3.as.all
=> [<#A _id: 1000000000000001>, <#A _id: 1000000000000002>] # i.e. a1, a2


Similar for
a
-s:

a1.bs.all
=> [<#B _id: 2000000000000001>, <#B _id: 2000000000000003>] # i.e. b1, b3

a2.bs.all
=> [<#B _id: 2000000000000002>, <#B _id: 2000000000000003>] # i.e. b2, b3

a3.bs.all
=> [<#B _id: 2000000000000002>] # i.e. b2


Now I want get those
a
-s which have
b
-s, which have


  • a3:


    • a1 has b1, b3 which have: [[a1],[a1, a2]] => [a1, a2] => don't include a1

    • a2 has b2, b3 which have: [[a2, a3],[a1, a2]] => [a1, a2, a3] => include a2

    • a3 has b2, which has [[a2, a3]] => [a2, a3] => include a3

      => [a2, a3] => [<#A _id: 1000000000000002>, <#A _id: 1000000000000003>]


  • both a1 and a2:


    • a1 has b1, b3 which have: [[a1],[a1, a2]] => [a1, a2] => include a1

    • a2 has b2, b3 which have: [[a2, a3],[a1, a2]] => [a1, a2, a3] => include a2

    • a3 has b2, which has [[a2, a3]] => [a2, a3] => don't include a3

      => [a1, a2] => [<#A _id: 1000000000000001>, <#A _id: 1000000000000002>]


  • don't include a1


    • a1 has b1, b3 which have: [[a1],[a1, a2]] => [a1, a2] => include a1

    • a2 has b2, b3 which have: [[a2, a3],[a1, a2]] => [a1, a2, a3] => include a2

    • a3 has b2, which has [[a2, a3]] => [a2, a3] => don't include a3

      => [a3] => [<#A _id: 1000000000000003>]


  • etc.



I thought it would be easy:

A.all.where("b.id" => a1._id).count
=> 0


or

A.all.where(:"bs.id" => a1._id).count
=> 0


or

A.all.where(:"b.id" => "1000000000000003").count
=> 0


But nope...

TLDP: how to find documents which have collections which have desired documents? (say multilevel query)?

Answer Source

That query for finding if HasMany (or HABTM) relation has relationshipt with another side (belongs_to or HABTM) is :

A.in(b_ids: [b1.id])
# return criteria for : All A's that b_ids has b1.id

It also works this way :

A.where(:b_ids.in => [b1.id])

If you want to query multiple items of array you must use mongodb $all operator :

A.where(:b_ids.all [b1.id,b2.id])
# return criteria for : All A's that b_ids has b1.id and b2.id

Mongoid/Origin.Selection Documentation

MongoDB $in operator

MongoDB $all operator