RoR3+Mongoid: How to compare fields of collection/table when querying?

Question

I have a structure that has both :id and a :group_id fields. I wish to retrieve all the data which :id equals :group_id.

I am currently working with mongoid, but I think that the operation is done the same way as it would be done when using Rails3's ActiveRecord.

Anyways. I was trying to use Message.where(:group_id => :id), which should return, for instance, this: => #<Message _id: 4e38aa132748440a7e00007d, group_id: BSON::ObjectId('4e38aa132748440a7e00007d')>. But it does not work at all (it seems as if it really should not work, but how it should, then?)

How can I query mongoid (or active record) to get only the Messages where :id equals :group_id?

EDIT: just to show that those values are really exactly the same:

ruby-1.9.2-p180 :088 > Message.first.id
=> BSON::ObjectId('4e38aa132748440a7e00007d')
ruby-1.9.2-p180 :089 > Message.first.group_id
=> BSON::ObjectId('4e38aa132748440a7e00007d')

EDIT2: These do not work (they have at least one message (the example above) that match)):

ruby-1.9.2-p180 :091 > Message.where("group_id = id").count
 => 0 
ruby-1.9.2-p180 :092 > Message.where("group_id == id").count
 => 0 

EDIT3: Here is an example of message that should be returned: http://cl.ly/3q1r20421S3P1921101D

EDIT4: Another piece of weirdness:

ruby-1.9.2-p180 :034 >  Message.where("this._id = this.group_id").each { 
|m| puts "#{m.id} = #{m.group_id}" }
4e38aa132748440a7e00007d = 4e38aa132748440a7e00007d
4e38aa132748440a7e00007e = 4e38aa132748440a7e00007d
4e38aa562748440a7e000084 = 4e38aa562748440a7e000084
4e38aa562748440a7e000085 = 4e38aa562748440a7e000084
4e38ac312748440a7e000095 = 4e38ac312748440a7e000095
4e38ac312748440a7e000096 = 4e38ac312748440a7e000095
4e38ac312748440a7e000097 = 4e38ac312748440a7e000095

Why it returns those values, even though in some cases they are not equal? Aaaarghhhhh

EDIT5: Another piece of weirdness and a small inconclusive conclusion:

ruby-1.9.2-p180 :090 >  Message.where("this.read == this.sender_deleted").each { 
|m| puts "#{m.read.to_s},#{m.sender_deleted.to_s}" }
true,true
false,false
false,false
false,false
false,false

My query works with boolean values! Hence, it SEEMS it is not working only with BSON::ObjectId() object comparisons (I also could not retrieve messages by doing Message.where(:id => Message.first.id [edit: I managed to do this by using :_id instead of :id, but this did not help me on the remaining of my problems]). How can I compare these damn identifiers on the same object? I really need to, and I am afraid there is no elegant alternative (storing those exact same IDs as strings would be just too weird, please do not suggest it :/ ). In the end, this seems like a specific issue to Mongoid.

obs.: I tried all combinations that came to mind, regarding id vs. _id and = vs. ==. Is there any Hash-like query for accomplishing what I need? using something like .where(:_id => :group_id) (but not that, I mean the :group_id value, not a :group_id symbol).

Answer 1

Try this:

Message.where("this.group_id == this.id")

Answer 2

Mongoid usees MongoDB in the end, so anything which is not possible in mongodb can not be done using mongoid. The particular use case you are talking about, is not supported by mongodb as of now.

You cannot query a field against a field, you have to provide literal values to query against. I saw an issue on Mongodb Issue Tracker for supporting this, but couldn't locate it now.

The boolean value query you are mentioning, might just be a coincidence. That should also not work as expected.

Update

One can compare fields in mongodb using $where clause.

Answer 3

You have been missing the '$where' in Mongoid's where method

This worked for me:

Message.where('$where' => 'this.group_id == this.id')

Answer 4

Message.where("group_id = id")