static static - 2 months ago 10
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

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

Comments