Repeated conditions in sql queries

Rails
1

This is a minor question, mostly for curiosity: using rails 2.3, does
anybody else notice repeated conditions in sql queries?

consider the following app with two models linked through a join
table:

class User < ActiveRecord::Base
has_many :boxes_users
has_many :boxes, :through => :boxes_users
end

class Box < ActiveRecord::Base
named_scope :brown, :conditions => {:color => ‘brown’}
end

class BoxesUser < ActiveRecord::Base
belongs_to :box
belongs_to :user
end

If I run this query:

User.find(1).boxes.brown

The following SQL is generated:

SELECT “boxes”.* FROM “boxes” INNER JOIN “boxes_users” ON “boxes”.id =
“boxes_users”.box_id WHERE ((“boxes_users”.user_id = 1)) AND
((“boxes”.“color” = ‘brown’) AND ((“boxes_users”.user_id = 1)))

Note that the (“boxes_users”.user_id = 1) condition is repeated in the
sql query. Think does not impact the query results (though it does
makes parsing the development log a bit more tedious). Just curious
if anyone knows if this could potentially lead to less efficient
database queries in some circumstances, or have other secondary
effects?

2

Barun S. wrote:
[…]

consider the following app with two models linked through a join
table:

class User < ActiveRecord::Base
has_many :boxes_users
has_many :boxes, :through => :boxes_users
end

class Box < ActiveRecord::Base
named_scope :brown, :conditions => {:color => ‘brown’}
end

class BoxesUser < ActiveRecord::Base
belongs_to :box
belongs_to :user
end

Off the main topic for a second: if the join table needs a separate
model, change its name to something more descriptive. “BoxesUser” is
not a terribly descriptive name. “Assignment” or “BoxAssignment” might
be clearer.

If I run this query:

User.find(1).boxes.brown

The following SQL is generated:

SELECT “boxes”.* FROM “boxes” INNER JOIN “boxes_users” ON “boxes”.id =
“boxes_users”.box_id WHERE ((“boxes_users”.user_id = 1)) AND
((“boxes”.“color” = ‘brown’) AND ((“boxes_users”.user_id = 1)))

Note that the (“boxes_users”.user_id = 1) condition is repeated in the
sql query. Think does not impact the query results (though it does
makes parsing the development log a bit more tedious). Just curious
if anyone knows if this could potentially lead to less efficient
database queries in some circumstances, or have other secondary
effects?

I doubt it. I would guess that the DB’s query optimizer would notice
the duplicate condition right away and remove it.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]