API tools faq
paste
Guest User

Untitled

a guest
Dec 10th, 2017
63
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 42.18 KB | None | 0 0
raw download clone embed print report
  1. diff --git a/CHANGELOG b/CHANGELOG
  2. index b735e0f..ee6361b 100644
  3. --- a/CHANGELOG
  4. +++ b/CHANGELOG
  5. @@ -1,5 +1,11 @@
  6. === HEAD
  7.  
  8. +* Add support for filtering and excluding by multiple associations (jeremyevans)
  9. +
  10. +* Add support for inverting some SQL::Constant instances such as TRUE, FALSE, NULL, and NOTNULL (jeremyevans)
  11. +
  12. +* Add support for excluding by associations to model datasets (jeremyevans)
  13. +
  14. * The Sequel::Postgres.use_iso_date_format setting now only affects future Database objects (jeremyevans)
  15.  
  16. * Add Sequel::Postgres::PG_NAMED_TYPES hash for extensions to register type conversions for non-standard types (jeremyevans, pvh)
  17. diff --git a/lib/sequel/model/associations.rb b/lib/sequel/model/associations.rb
  18. index 84117b6..a0dc083 100644
  19. --- a/lib/sequel/model/associations.rb
  20. +++ b/lib/sequel/model/associations.rb
  21. @@ -1415,14 +1415,21 @@ module Sequel
  22. # types, this is a simple transformation, but for +many_to_many+ associations this
  23. # creates a subquery to the join table.
  24. def complex_expression_sql(op, args)
  25. - if op == :'=' and args.at(1).is_a?(Sequel::Model)
  26. - l, r = args
  27. + r = args.at(1)
  28. + if (((op == :'=' || op == :'!=') and r.is_a?(Sequel::Model)) ||
  29. + (multiple = ((op == :IN || op == :'NOT IN') and !r.is_a?(Sequel::Dataset) and r.all?{|x| x.is_a?(Sequel::Model)})))
  30. + l = args.at(0)
  31. if ar = model.association_reflections[l]
  32. - unless r.is_a?(ar.associated_class)
  33. + if multiple
  34. + klass = ar.associated_class
  35. + unless r.all?{|x| x.is_a?(klass)}
  36. + raise Sequel::Error, "invalid association class for one object for association #{l.inspect} used in dataset filter for model #{model.inspect}, expected class #{klass.inspect}"
  37. + end
  38. + elsif !r.is_a?(ar.associated_class)
  39. raise Sequel::Error, "invalid association class #{r.class.inspect} for association #{l.inspect} used in dataset filter for model #{model.inspect}, expected class #{ar.associated_class.inspect}"
  40. end
  41.  
  42. - if exp = association_filter_expression(ar, r)
  43. + if exp = association_filter_expression(op, ar, r)
  44. literal(exp)
  45. else
  46. raise Sequel::Error, "invalid association type #{ar[:type].inspect} for association #{l.inspect} used in dataset filter for model #{model.inspect}"
  47. @@ -1577,9 +1584,42 @@ module Sequel
  48. private
  49.  
  50. # Return an expression for filtering by the given association reflection and associated object.
  51. - def association_filter_expression(ref, obj)
  52. + def association_filter_expression(op, ref, obj)
  53. meth = :"#{ref[:type]}_association_filter_expression"
  54. - send(meth, ref, obj) if respond_to?(meth, true)
  55. + send(meth, op, ref, obj) if respond_to?(meth, true)
  56. + end
  57. +
  58. + # Handle inversion for association filters by returning an inverted expression,
  59. + # plus also handling cases where the referenced columns are NULL.
  60. + def association_filter_handle_inversion(op, exp, cols)
  61. + if op == :'!=' || op == :'NOT IN'
  62. + if exp == SQL::Constants::FALSE
  63. + ~exp
  64. + else
  65. + ~exp | Sequel::SQL::BooleanExpression.from_value_pairs(cols.zip([]), :OR)
  66. + end
  67. + else
  68. + exp
  69. + end
  70. + end
  71. +
  72. + # Return an expression for making sure that the given keys match the value of
  73. + # the given methods for either the single object given or for any of the objects
  74. + # given if +obj+ is an array.
  75. + def association_filter_key_expression(keys, meths, obj)
  76. + vals = Array(obj).reject{|o| !meths.all?{|m| o.send(m)}}
  77. + return SQL::Constants::FALSE if vals.empty?
  78. + vals = if obj.is_a?(Array)
  79. + if keys.length == 1
  80. + meth = meths.first
  81. + {keys.first=>vals.map{|o| o.send(meth)}}
  82. + else
  83. + {keys=>vals.map{|o| meths.map{|m| o.send(m)}}}
  84. + end
  85. + else
  86. + keys.zip(meths.map{|k| obj.send(k)})
  87. + end
  88. + SQL::BooleanExpression.from_value_pairs(vals)
  89. end
  90.  
  91. # Make sure the association is valid for this model, and return the related AssociationReflection.
  92. @@ -1685,20 +1725,27 @@ module Sequel
  93. end
  94.  
  95. # Return a subquery expression for filering by a many_to_many association
  96. - def many_to_many_association_filter_expression(ref, obj)
  97. - lpks, lks, rks = ref.values_at(:left_primary_keys, :left_keys, :right_keys)
  98. - lpks = lpks.first if lpks.length == 1
  99. - SQL::BooleanExpression.from_value_pairs(lpks=>model.db[ref[:join_table]].select(*lks).where(rks.zip(ref.right_primary_keys.map{|k| obj.send(k)})))
  100. + def many_to_many_association_filter_expression(op, ref, obj)
  101. + lpks, lks, rks = ref.values_at(:left_primary_keys, :left_keys, :right_keys)
  102. + lpks = lpks.first if lpks.length == 1
  103. + exp = association_filter_key_expression(rks, ref.right_primary_keys, obj)
  104. + if exp == SQL::Constants::FALSE
  105. + association_filter_handle_inversion(op, exp, Array(lpks))
  106. + else
  107. + association_filter_handle_inversion(op, SQL::BooleanExpression.from_value_pairs(lpks=>model.db[ref[:join_table]].select(*lks).where(exp).exclude(SQL::BooleanExpression.from_value_pairs(lks.zip([]), :OR))), Array(lpks))
  108. + end
  109. end
  110.  
  111. # Return a simple equality expression for filering by a many_to_one association
  112. - def many_to_one_association_filter_expression(ref, obj)
  113. - SQL::BooleanExpression.from_value_pairs(ref[:keys].zip(ref.primary_keys.map{|k| obj.send(k)}))
  114. + def many_to_one_association_filter_expression(op, ref, obj)
  115. + keys = ref[:keys]
  116. + association_filter_handle_inversion(op, association_filter_key_expression(keys, ref.primary_keys, obj), keys)
  117. end
  118.  
  119. # Return a simple equality expression for filering by a one_to_* association
  120. - def one_to_many_association_filter_expression(ref, obj)
  121. - SQL::BooleanExpression.from_value_pairs(ref[:primary_keys].zip(ref[:keys].map{|k| obj.send(k)}))
  122. + def one_to_many_association_filter_expression(op, ref, obj)
  123. + keys = ref[:primary_keys]
  124. + association_filter_handle_inversion(op, association_filter_key_expression(keys, ref[:keys], obj), keys)
  125. end
  126. alias one_to_one_association_filter_expression one_to_many_association_filter_expression
  127.  
  128. diff --git a/lib/sequel/plugins/many_through_many.rb b/lib/sequel/plugins/many_through_many.rb
  129. index b495051..7f4c34e 100644
  130. --- a/lib/sequel/plugins/many_through_many.rb
  131. +++ b/lib/sequel/plugins/many_through_many.rb
  132. @@ -224,7 +224,7 @@ module Sequel
  133. private
  134.  
  135. # Use a subquery to filter rows to those related to the given associated object
  136. - def many_through_many_association_filter_expression(ref, obj)
  137. + def many_through_many_association_filter_expression(op, ref, obj)
  138. lpks = ref[:left_primary_keys]
  139. lpks = lpks.first if lpks.length == 1
  140. edges = ref.edges
  141. @@ -238,8 +238,8 @@ module Sequel
  142. last_join = ds.opts[:join].last
  143. last_join.table_alias || last_join.table
  144. end
  145. - ds = ds.where(Array(ref[:final_edge][:left]).map{|x| ::Sequel::SQL::QualifiedIdentifier.new(last_alias, x)}.zip(ref.right_primary_keys.map{|k| obj.send(k)}))
  146. - SQL::BooleanExpression.from_value_pairs(lpks=>ds)
  147. + ds = ds.where(Array(ref[:final_edge][:left]).map{|x| ::Sequel::SQL::QualifiedIdentifier.new(last_alias, x)}.zip(ref.right_primary_keys.map{|k| obj.send(k)})).exclude(SQL::BooleanExpression.from_value_pairs(ds.opts[:select].zip([]), :OR))
  148. + association_filter_handle_inversion(op, SQL::BooleanExpression.from_value_pairs(lpks=>ds), Array(lpks))
  149. end
  150. end
  151. end
  152. diff --git a/lib/sequel/sql.rb b/lib/sequel/sql.rb
  153. index 80a7aaa..36f56e5 100644
  154. --- a/lib/sequel/sql.rb
  155. +++ b/lib/sequel/sql.rb
  156. @@ -595,6 +595,8 @@ module Sequel
  157. end
  158. when StringExpression, NumericExpression
  159. raise(Sequel::Error, "cannot invert #{ce.inspect}")
  160. + when Constant
  161. + CONSTANT_INVERSIONS[ce] || raise(Sequel::Error, "cannot invert #{ce.inspect}")
  162. else
  163. BooleanExpression.new(:NOT, ce)
  164. end
  165. @@ -706,6 +708,12 @@ module Sequel
  166. NOTNULL = NegativeBooleanConstant.new(nil)
  167. end
  168.  
  169. + class ComplexExpression
  170. + # A hash of the opposite for each constant, used for inverting constants.
  171. + CONSTANT_INVERSIONS = {Constants::TRUE=>Constants::FALSE, Constants::FALSE=>Constants::TRUE,
  172. + Constants::NULL=>Constants::NOTNULL, Constants::NOTNULL=>Constants::NULL}
  173. + end
  174. +
  175. # Represents an SQL function call.
  176. class Function < GenericExpression
  177. # The array of arguments to pass to the function (may be blank)
  178. diff --git a/spec/core/expression_filters_spec.rb b/spec/core/expression_filters_spec.rb
  179. index 492f8f1..3c6de5d 100644
  180. --- a/spec/core/expression_filters_spec.rb
  181. +++ b/spec/core/expression_filters_spec.rb
  182. @@ -424,6 +424,19 @@ describe "Blockless Ruby Filters" do
  183. @d.l(~{:x => Sequel::SQLFALSE}).should == '(x IS NOT FALSE)'
  184. end
  185.  
  186. + it "should support direct negation of SQL::Constants" do
  187. + @d.l({:x => ~Sequel::NULL}).should == '(x IS NOT NULL)'
  188. + @d.l({:x => ~Sequel::NOTNULL}).should == '(x IS NULL)'
  189. + @d.l({:x => ~Sequel::TRUE}).should == '(x IS FALSE)'
  190. + @d.l({:x => ~Sequel::FALSE}).should == '(x IS TRUE)'
  191. + @d.l({:x => ~Sequel::SQLTRUE}).should == '(x IS FALSE)'
  192. + @d.l({:x => ~Sequel::SQLFALSE}).should == '(x IS TRUE)'
  193. + end
  194. +
  195. + it "should raise an error if trying to invert an invalid SQL::Constant" do
  196. + proc{~Sequel::CURRENT_DATE}.should raise_error(Sequel::Error)
  197. + end
  198. +
  199. it "should raise an error if trying to create an invalid complex expression" do
  200. proc{Sequel::SQL::ComplexExpression.new(:BANG, 1, 2)}.should raise_error(Sequel::Error)
  201. end
  202. diff --git a/spec/extensions/many_through_many_spec.rb b/spec/extensions/many_through_many_spec.rb
  203. index e68a8b3..55c9fcb 100644
  204. --- a/spec/extensions/many_through_many_spec.rb
  205. +++ b/spec/extensions/many_through_many_spec.rb
  206. @@ -114,24 +114,35 @@ describe Sequel::Model, "many_through_many" do
  207.  
  208. it "should allowing filtering by many_through_many associations" do
  209. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  210. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE (albums_tags.tag_id = 1234)))'
  211. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  212. end
  213.  
  214. it "should allowing filtering by many_through_many associations with a single through table" do
  215. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id]]
  216. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists WHERE (albums_artists.album_id = 1234)))'
  217. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists WHERE ((albums_artists.album_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  218. end
  219.  
  220. it "should allowing filtering by many_through_many associations with aliased tables" do
  221. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums_artists, :id, :id], [:albums_artists, :album_id, :tag_id]]
  222. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums_artists AS albums_artists_0 ON (albums_artists_0.id = albums_artists.album_id) INNER JOIN albums_artists AS albums_artists_1 ON (albums_artists_1.album_id = albums_artists_0.id) WHERE (albums_artists_1.tag_id = 1234)))'
  223. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums_artists AS albums_artists_0 ON (albums_artists_0.id = albums_artists.album_id) INNER JOIN albums_artists AS albums_artists_1 ON (albums_artists_1.album_id = albums_artists_0.id) WHERE ((albums_artists_1.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  224. end
  225.  
  226. it "should allowing filtering by many_through_many associations with composite keys" do
  227. @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  228. - @c1.filter(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE ((id, yyy) IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85))))'
  229. + @c1.filter(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE ((id, yyy) IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL))))'
  230. end
  231.  
  232. + it "should allowing excluding by many_through_many associations" do
  233. + @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  234. + @c1.exclude(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE ((id NOT IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL)))) OR (id IS NULL))'
  235. + end
  236. +
  237. + it "should allowing excluding by many_through_many associations with composite keys" do
  238. + @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  239. + @c1.exclude(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE (((id, yyy) NOT IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL)))) OR (id IS NULL) OR (yyy IS NULL))'
  240. + end
  241. +
  242. +
  243. it "should support a :conditions option" do
  244. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]], :conditions=>{:a=>32}
  245. n = @c1.load(:id => 1234)
  246. diff --git a/spec/integration/associations_test.rb b/spec/integration/associations_test.rb
  247. index b2fe47a..f0d6cfa 100644
  248. --- a/spec/integration/associations_test.rb
  249. +++ b/spec/integration/associations_test.rb
  250. @@ -26,10 +26,6 @@ shared_examples_for "regular and composite key associations" do
  251. @album.update(:artist => @artist)
  252. @album.add_tag(@tag)
  253.  
  254. - @album.reload
  255. - @artist.reload
  256. - @tag.reload
  257. -
  258. Artist.filter(:albums=>@album).all.should == [@artist]
  259. Album.filter(:artist=>@artist).all.should == [@album]
  260. Album.filter(:tags=>@tag).all.should == [@album]
  261. @@ -37,7 +33,40 @@ shared_examples_for "regular and composite key associations" do
  262. Album.filter(:artist=>@artist, :tags=>@tag).all.should == [@album]
  263. @artist.albums_dataset.filter(:tags=>@tag).all.should == [@album]
  264. end
  265. +
  266. + specify "should work correctly when excluding by associations" do
  267. + @album.update(:artist => @artist)
  268. + @album.add_tag(@tag)
  269. + album, artist, tag = @pr.call
  270. +
  271. + Artist.exclude(:albums=>@album).all.should == [artist]
  272. + Album.exclude(:artist=>@artist).all.should == [album]
  273. + Album.exclude(:tags=>@tag).all.should == [album]
  274. + Tag.exclude(:albums=>@album).all.should == [tag]
  275. + Album.exclude(:artist=>@artist, :tags=>@tag).all.should == [album]
  276. + end
  277.  
  278. + specify "should work correctly when excluding by associations in regards to NULL values" do
  279. + Artist.exclude(:albums=>@album).all.should == [@artist]
  280. + Album.exclude(:artist=>@artist).all.should == [@album]
  281. + Album.exclude(:tags=>@tag).all.should == [@album]
  282. + Tag.exclude(:albums=>@album).all.should == [@tag]
  283. + Album.exclude(:artist=>@artist, :tags=>@tag).all.should == [@album]
  284. +
  285. + @album.update(:artist => @artist)
  286. + @artist.albums_dataset.exclude(:tags=>@tag).all.should == [@album]
  287. + end
  288. +
  289. + specify "should handle NULL values in join table correctly when filtering/excluding many_to_many associations" do
  290. + @ins.call
  291. + Album.exclude(:tags=>@tag).all.should == [@album]
  292. + @album.add_tag(@tag)
  293. + Album.filter(:tags=>@tag).all.should == [@album]
  294. + album, artist, tag = @pr.call
  295. + Album.exclude(:tags=>@tag).all.should == [album]
  296. + Album.exclude(:tags=>tag).all.sort_by{|x| x.pk}.should == [@album, album]
  297. + end
  298. +
  299. specify "should have remove methods work" do
  300. @album.update(:artist => @artist)
  301. @album.add_tag(@tag)
  302. @@ -167,6 +196,8 @@ describe "Sequel::Model Simple Associations" do
  303. @album = Album.create(:name=>'Al')
  304. @artist = Artist.create(:name=>'Ar')
  305. @tag = Tag.create(:name=>'T')
  306. + @pr = lambda{[Album.create(:name=>'Al2'),Artist.create(:name=>'Ar2'),Tag.create(:name=>'T2')]}
  307. + @ins = lambda{@db[:albums_tags].insert(:tag_id=>@tag.id)}
  308. end
  309. after do
  310. @db.drop_table(:albums_tags, :tags, :albums, :artists)
  311. @@ -333,6 +364,8 @@ describe "Sequel::Model Composite Key Associations" do
  312. @album = Album.create(:name=>'Al', :id1=>1, :id2=>2)
  313. @artist = Artist.create(:name=>'Ar', :id1=>3, :id2=>4)
  314. @tag = Tag.create(:name=>'T', :id1=>5, :id2=>6)
  315. + @pr = lambda{[Album.create(:name=>'Al2', :id1=>11, :id2=>12),Artist.create(:name=>'Ar2', :id1=>13, :id2=>14),Tag.create(:name=>'T2', :id1=>15, :id2=>16)]}
  316. + @ins = lambda{@db[:albums_tags].insert(:tag_id1=>@tag.id1, :tag_id2=>@tag.id2)}
  317. end
  318. after do
  319. @db.drop_table(:albums_tags, :tags, :albums, :artists)
  320. diff --git a/spec/integration/plugin_test.rb b/spec/integration/plugin_test.rb
  321. index b454484..6fbc4e2 100644
  322. --- a/spec/integration/plugin_test.rb
  323. +++ b/spec/integration/plugin_test.rb
  324. @@ -210,6 +210,11 @@ describe "Many Through Many Plugin" do
  325. Artist.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
  326. Artist.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
  327. Artist.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'
  328. +
  329. + Artist.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
  330. + Artist.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
  331. + Artist.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
  332. + Artist.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'
  333. end
  334.  
  335. specify "should handle typical case with 3 join tables" do
  336. @@ -233,6 +238,11 @@ describe "Many Through Many Plugin" do
  337. Artist.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
  338. Artist.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
  339. Artist.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'
  340. +
  341. + Artist.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
  342. + Artist.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
  343. + Artist.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
  344. + Artist.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'
  345. end
  346.  
  347. specify "should handle extreme case with 5 join tables" do
  348. @@ -265,6 +275,11 @@ describe "Many Through Many Plugin" do
  349. Artist.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
  350. Artist.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
  351. Artist.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'
  352. +
  353. + Artist.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
  354. + Artist.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
  355. + Artist.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
  356. + Artist.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'
  357. end
  358. end
  359.  
  360. diff --git a/spec/model/associations_spec.rb b/spec/model/associations_spec.rb
  361. index 2f0a601..9e16278 100644
  362. --- a/spec/model/associations_spec.rb
  363. +++ b/spec/model/associations_spec.rb
  364. @@ -2795,7 +2795,7 @@ describe "Filtering by associations" do
  365. end
  366.  
  367. it "should be able to filter on many_to_many associations" do
  368. - @Album.filter(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE (tag_id = 3)))'
  369. + @Album.filter(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id = 3) AND (album_id IS NOT NULL))))'
  370. end
  371.  
  372. it "should be able to filter on many_to_one associations with composite keys" do
  373. @@ -2811,7 +2811,7 @@ describe "Filtering by associations" do
  374. end
  375.  
  376. it "should be able to filter on many_to_many associations with composite keys" do
  377. - @Album.filter(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4))))'
  378. + @Album.filter(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  379. end
  380.  
  381. it "should work inside a complex filter" do
  382. @@ -2840,4 +2840,261 @@ describe "Filtering by associations" do
  383. c.many_to_one :sartist, :class=>@Artist
  384. c.filter(:sartist=>@Artist.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (sartist_id = 3)'
  385. end
  386. +
  387. + it "should be able to exclude on many_to_one associations" do
  388. + @Album.exclude(:artist=>@Artist.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE ((artist_id != 3) OR (artist_id IS NULL))'
  389. + end
  390. +
  391. + it "should be able to exclude on one_to_many associations" do
  392. + @Album.exclude(:tracks=>@Track.load(:album_id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id != 3) OR (id IS NULL))'
  393. + end
  394. +
  395. + it "should be able to exclude on one_to_one associations" do
  396. + @Album.exclude(:album_info=>@AlbumInfo.load(:album_id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id != 3) OR (id IS NULL))'
  397. + end
  398. +
  399. + it "should be able to exclude on many_to_many associations" do
  400. + @Album.exclude(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id = 3) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  401. + end
  402. +
  403. + it "should be able to exclude on many_to_one associations with composite keys" do
  404. + @Album.exclude(:cartist=>@Artist.load(:id1=>3, :id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((artist_id1 != 3) OR (artist_id2 != 4) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  405. + end
  406. +
  407. + it "should be able to exclude on one_to_many associations with composite keys" do
  408. + @Album.exclude(:ctracks=>@Track.load(:album_id1=>3, :album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1 != 3) OR (id2 != 4) OR (id1 IS NULL) OR (id2 IS NULL))'
  409. + end
  410. +
  411. + it "should be able to exclude on one_to_one associations with composite keys" do
  412. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id1=>3, :album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1 != 3) OR (id2 != 4) OR (id1 IS NULL) OR (id2 IS NULL))'
  413. + end
  414. +
  415. + it "should be able to exclude on many_to_many associations with composite keys" do
  416. + @Album.exclude(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  417. + end
  418. +
  419. + it "should be able to filter on multiple many_to_one associations" do
  420. + @Album.filter(:artist=>[@Artist.load(:id=>3), @Artist.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE (artist_id IN (3, 4))'
  421. + end
  422. +
  423. + it "should be able to filter on multiple one_to_many associations" do
  424. + @Album.filter(:tracks=>[@Track.load(:album_id=>3), @Track.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (3, 4))'
  425. + end
  426. +
  427. + it "should be able to filter on multiple one_to_one associations" do
  428. + @Album.filter(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (3, 4))'
  429. + end
  430. +
  431. + it "should be able to filter on multiple many_to_many associations" do
  432. + @Album.filter(:tags=>[@Tag.load(:id=>3), @Tag.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3, 4)) AND (album_id IS NOT NULL))))'
  433. + end
  434. +
  435. + it "should be able to filter on multiple many_to_one associations with composite keys" do
  436. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>5, :id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4), (5, 6)))'
  437. + end
  438. +
  439. + it "should be able to filter on multiple one_to_many associations with composite keys" do
  440. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4), (5, 6)))'
  441. + end
  442. +
  443. + it "should be able to filter on multiple one_to_one associations with composite keys" do
  444. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4), (5, 6)))'
  445. + end
  446. +
  447. + it "should be able to filter on multiple many_to_many associations with composite keys" do
  448. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5, :tid2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4), (5, 6))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  449. + end
  450. +
  451. + it "should be able to exclude on multiple many_to_one associations" do
  452. + @Album.exclude(:artist=>[@Artist.load(:id=>3), @Artist.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id NOT IN (3, 4)) OR (artist_id IS NULL))'
  453. + end
  454. +
  455. + it "should be able to exclude on multiple one_to_many associations" do
  456. + @Album.exclude(:tracks=>[@Track.load(:album_id=>3), @Track.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3, 4)) OR (id IS NULL))'
  457. + end
  458. +
  459. + it "should be able to exclude on multiple one_to_one associations" do
  460. + @Album.exclude(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3, 4)) OR (id IS NULL))'
  461. + end
  462. +
  463. + it "should be able to exclude on multiple many_to_many associations" do
  464. + @Album.exclude(:tags=>[@Tag.load(:id=>3), @Tag.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3, 4)) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  465. + end
  466. +
  467. + it "should be able to exclude on multiple many_to_one associations with composite keys" do
  468. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>5, :id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4), (5, 6))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  469. + end
  470. +
  471. + it "should be able to exclude on multiple one_to_many associations with composite keys" do
  472. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4), (5, 6))) OR (id1 IS NULL) OR (id2 IS NULL))'
  473. + end
  474. +
  475. + it "should be able to exclude on multiple one_to_one associations with composite keys" do
  476. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4), (5, 6))) OR (id1 IS NULL) OR (id2 IS NULL))'
  477. + end
  478. +
  479. + it "should be able to exclude on multiple many_to_many associations with composite keys" do
  480. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5, :tid2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4), (5, 6))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  481. + end
  482. +
  483. + it "should be able to handle NULL values when filtering many_to_one associations" do
  484. + @Album.filter(:artist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  485. + end
  486. +
  487. + it "should be able to handle NULL values when filtering one_to_many associations" do
  488. + @Album.filter(:tracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  489. + end
  490. +
  491. + it "should be able to handle NULL values when filtering one_to_one associations" do
  492. + @Album.filter(:album_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  493. + end
  494. +
  495. + it "should be able to handle NULL values when filtering many_to_many associations" do
  496. + @Album.filter(:tags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  497. + end
  498. +
  499. + it "should be able to handle filteringing with NULL values for many_to_one associations with composite keys" do
  500. + @Album.filter(:cartist=>@Artist.load(:id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  501. + @Album.filter(:cartist=>@Artist.load(:id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  502. + @Album.filter(:cartist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  503. + end
  504. +
  505. + it "should be able to filtering with NULL values for one_to_many associations with composite keys" do
  506. + @Album.filter(:ctracks=>@Track.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  507. + @Album.filter(:ctracks=>@Track.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  508. + @Album.filter(:ctracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  509. + end
  510. +
  511. + it "should be able to filtering with NULL values for one_to_one associations with composite keys" do
  512. + @Album.filter(:calbum_info=>@AlbumInfo.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  513. + @Album.filter(:calbum_info=>@AlbumInfo.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  514. + @Album.filter(:calbum_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  515. + end
  516. +
  517. + it "should be able to filtering with NULL values for many_to_many associations with composite keys" do
  518. + @Album.filter(:ctags=>@Tag.load(:tid1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  519. + @Album.filter(:ctags=>@Tag.load(:tid2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  520. + @Album.filter(:ctags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  521. + end
  522. +
  523. + it "should be able to handle NULL values when excluding many_to_one associations" do
  524. + @Album.exclude(:artist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  525. + end
  526. +
  527. + it "should be able to handle NULL values when excluding one_to_many associations" do
  528. + @Album.exclude(:tracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  529. + end
  530. +
  531. + it "should be able to handle NULL values when excluding one_to_one associations" do
  532. + @Album.exclude(:album_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  533. + end
  534. +
  535. + it "should be able to handle NULL values when excluding many_to_many associations" do
  536. + @Album.exclude(:tags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  537. + end
  538. +
  539. + it "should be able to handle excluding with NULL values for many_to_one associations with composite keys" do
  540. + @Album.exclude(:cartist=>@Artist.load(:id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  541. + @Album.exclude(:cartist=>@Artist.load(:id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  542. + @Album.exclude(:cartist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  543. + end
  544. +
  545. + it "should be able to excluding with NULL values for one_to_many associations with composite keys" do
  546. + @Album.exclude(:ctracks=>@Track.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  547. + @Album.exclude(:ctracks=>@Track.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  548. + @Album.exclude(:ctracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  549. + end
  550. +
  551. + it "should be able to excluding with NULL values for one_to_one associations with composite keys" do
  552. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  553. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  554. + @Album.exclude(:calbum_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  555. + end
  556. +
  557. + it "should be able to excluding with NULL values for many_to_many associations with composite keys" do
  558. + @Album.exclude(:ctags=>@Tag.load(:tid1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  559. + @Album.exclude(:ctags=>@Tag.load(:tid2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  560. + @Album.exclude(:ctags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  561. + end
  562. +
  563. + it "should be able to handle NULL values when filtering multiple many_to_one associations" do
  564. + @Album.filter(:artist=>[@Artist.load(:id=>3), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE (artist_id IN (3))'
  565. + @Album.filter(:artist=>[@Artist.new, @Artist.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  566. + end
  567. +
  568. + it "should be able to handle NULL values when filtering multiple one_to_many associations" do
  569. + @Album.filter(:tracks=>[@Track.load(:album_id=>3), @Track.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (3))'
  570. + @Album.filter(:tracks=>[@Track.new, @Track.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  571. + end
  572. +
  573. + it "should be able to handle NULL values when filtering multiple one_to_one associations" do
  574. + @Album.filter(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (3))'
  575. + @Album.filter(:album_info=>[@AlbumInfo.new, @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  576. + end
  577. +
  578. + it "should be able to handle NULL values when filtering multiple many_to_many associations" do
  579. + @Album.filter(:tags=>[@Tag.load(:id=>3), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3)) AND (album_id IS NOT NULL))))'
  580. + @Album.filter(:tags=>[@Tag.new, @Tag.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  581. + end
  582. +
  583. + it "should be able to handle NULL values when filtering multiple many_to_one associations with composite keys" do
  584. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>3)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4)))'
  585. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4)))'
  586. + end
  587. +
  588. + it "should be able handle NULL values when filtering multiple one_to_many associations with composite keys" do
  589. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>3)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  590. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  591. + end
  592. +
  593. + it "should be able to handle NULL values when filtering multiple one_to_one associations with composite keys" do
  594. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  595. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  596. + end
  597. +
  598. + it "should be able to handle NULL values when filtering multiple many_to_many associations with composite keys" do
  599. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  600. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  601. + end
  602. +
  603. + it "should be able to handle NULL values when excluding multiple many_to_one associations" do
  604. + @Album.exclude(:artist=>[@Artist.load(:id=>3), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE ((artist_id NOT IN (3)) OR (artist_id IS NULL))'
  605. + @Album.exclude(:artist=>[@Artist.new, @Artist.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  606. + end
  607. +
  608. + it "should be able to handle NULL values when excluding multiple one_to_many associations" do
  609. + @Album.exclude(:tracks=>[@Track.load(:album_id=>3), @Track.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3)) OR (id IS NULL))'
  610. + @Album.exclude(:tracks=>[@Track.new, @Track.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  611. + end
  612. +
  613. + it "should be able to handle NULL values when excluding multiple one_to_one associations" do
  614. + @Album.exclude(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3)) OR (id IS NULL))'
  615. + @Album.exclude(:album_info=>[@AlbumInfo.new, @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  616. + end
  617. +
  618. + it "should be able to handle NULL values when excluding multiple many_to_many associations" do
  619. + @Album.exclude(:tags=>[@Tag.load(:id=>3), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3)) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  620. + @Album.exclude(:tags=>[@Tag.new, @Tag.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  621. + end
  622. +
  623. + it "should be able to handle NULL values when excluding multiple many_to_one associations with composite keys" do
  624. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>3)]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  625. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  626. + end
  627. +
  628. + it "should be able handle NULL values when excluding multiple one_to_many associations with composite keys" do
  629. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>3)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  630. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  631. + end
  632. +
  633. + it "should be able to handle NULL values when excluding multiple one_to_one associations with composite keys" do
  634. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  635. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  636. + end
  637. +
  638. + it "should be able to handle NULL values when excluding multiple many_to_many associations with composite keys" do
  639. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  640. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  641. + end
  642. +
  643. end
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!