I would very much be able to do this query with ActiveRecord:
SELECT `phones`.* FROM `phones` WHERE (ddd, number) in
((31,32310512),(31,96438112))
Unfortunately, ActiveRecord gives me no way to do so with a prepared
statement. The intuitive way to do it would be:
Phone.where(''(ddd, number) in (?)'',
[[31,32310512],[31,96438112]])
that does not work, as it yields:
SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 31\n-
32310512\n'',''---\n- 31\n- 96438112\n''))
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
I think this syntax wouldn''t be supported in SQLite based on this SO answer: http://stackoverflow.com/questions/1474964/using-tuples-in-sql-in-clause Maybe there is a better way to write this query? On Tue, May 21, 2013 at 12:13 PM, Rafael Almeida <almeidaraf@gmail.com> wrote:> I would very much be able to do this query with ActiveRecord: > SELECT `phones`.* FROM `phones` WHERE (ddd, number) in > ((31,32310512),(31,96438112)) > Unfortunately, ActiveRecord gives me no way to do so with a prepared > statement. The intuitive way to do it would be: > Phone.where(''(ddd, number) in (?)'', [[31,32310512],[31,96438112]]) > that does not work, as it yields: > SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 31\n- > 32310512\n'',''---\n- 31\n- 96438112\n'')) > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. > To post to this group, send email to rubyonrails-core@googlegroups.com. > Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. > For more options, visit https://groups.google.com/groups/opt_out.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
I can''t think of a better way, can you? Maybe I''m not strong enough with sql to see it. Maybe there are other ways to do it, but I hardly think it would be better. Anyway, since keeping compatible with sqlite is important, maybe there could be an "extra-mysql-features" sort of gem to extend ActiveRecord behaviour. Is there something like that already? I know there are a few extensions to activerecord behaviour, but is there something targeting making it support more features of certain databases? On Tuesday, May 21, 2013 1:18:36 PM UTC-3, Ryan Bigg wrote:> > I think this syntax wouldn''t be supported in SQLite based on this SO > answer: > http://stackoverflow.com/questions/1474964/using-tuples-in-sql-in-clause > > Maybe there is a better way to write this query? > > > On Tue, May 21, 2013 at 12:13 PM, Rafael Almeida <almei...@gmail.com<javascript:> > > wrote: > >> I would very much be able to do this query with ActiveRecord: >> >> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in >> ((31,32310512),(31,96438112)) >> >> Unfortunately, ActiveRecord gives me no way to do so with a prepared >> statement. The intuitive way to do it would be: >> >> Phone.where(''(ddd, number) in (?)'', [[31,32310512],[31,96438112]]) >> >> that does not work, as it yields: >> >> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 31\n- >> 32310512\n'',''---\n- 31\n- 96438112\n'')) >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Ruby on Rails: Core" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to rubyonrails-co...@googlegroups.com <javascript:>. >> To post to this group, send email to rubyonra...@googlegroups.com<javascript:> >> . >> Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en >> . >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
I did write something trivial <https://github.com/andhapp/tune_my_query> a couple of years ago that ensured ILIKE (postgres on production) was switched to LIKE (mysql on development) for gemcutter (rubygems now-a-days). It never got used though, as gemcutter team found a better way to do it. I never really found another use-case to develop it further. More than happy to work on it with someone. Anuj On 22 May 2013 03:07, Rafael Almeida <almeidaraf@gmail.com> wrote:> I can''t think of a better way, can you? Maybe I''m not strong enough with > sql to see it. Maybe there are other ways to do it, but I hardly think it > would be better. Anyway, since keeping compatible with sqlite is important, > maybe there could be an "extra-mysql-features" sort of gem to extend > ActiveRecord behaviour. Is there something like that already? I know there > are a few extensions to activerecord behaviour, but is there something > targeting making it support more features of certain databases? > > On Tuesday, May 21, 2013 1:18:36 PM UTC-3, Ryan Bigg wrote: >> >> I think this syntax wouldn''t be supported in SQLite based on this SO >> answer: http://stackoverflow.**com/questions/1474964/using-** >> tuples-in-sql-in-clause<http://stackoverflow.com/questions/1474964/using-tuples-in-sql-in-clause> >> >> Maybe there is a better way to write this query? >> >> >> On Tue, May 21, 2013 at 12:13 PM, Rafael Almeida <almei...@gmail.com>wrote: >> >>> I would very much be able to do this query with ActiveRecord: >>> >>> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in >>> ((31,32310512),(31,96438112)) >>> >>> Unfortunately, ActiveRecord gives me no way to do so with a prepared >>> statement. The intuitive way to do it would be: >>> >>> Phone.where(''(ddd, number) in (?)'', [[31,32310512],[31,96438112]]** >>> ) >>> >>> that does not work, as it yields: >>> >>> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 31\n- >>> 32310512\n'',''---\n- 31\n- 96438112\n'')) >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Ruby on Rails: Core" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to rubyonrails-co...@**googlegroups.com. >>> To post to this group, send email to rubyonra...@googlegroups.**com. >>> Visit this group at http://groups.google.com/** >>> group/rubyonrails-core?hl=en<http://groups.google.com/group/rubyonrails-core?hl=en> >>> . >>> For more options, visit https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>> . >>> >>> >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to rubyonrails-core+unsubscribe@googlegroups.com. > To post to this group, send email to rubyonrails-core@googlegroups.com. > Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > >-- Anuj DUTTA -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
I was going to joke that you may as well add support for SPARQL, since for
some reason it comes to mind when I think of tuples, and then found
something that already supports SPARQL with ActiveModel:
https://github.com/Swirrl/tripod
Does this work?
Phone.where(''(ddd, number) in ((?),(?))'', 31,32310512,
31,96438112)
That looks cleaner to me. If you need to send in an array:
Phone.where(''(ddd, number) in ((?),(?))'', *[31,32310512,
31,96438112])
or your example:
Phone.where(''(ddd, number) in ((?),(?))'', *
[[31,32310512],[31,96438112]].flatten)
and you could always construct the "(?),(?)" by counting the flattened
array to see how many ?''s you need.
On Tuesday, May 21, 2013 12:12:56 PM UTC-4, Rafael Almeida
wrote:>
> I would very much be able to do this query with ActiveRecord:
>
> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in
> ((31,32310512),(31,96438112))
>
> Unfortunately, ActiveRecord gives me no way to do so with a prepared
> statement. The intuitive way to do it would be:
>
> Phone.where(''(ddd, number) in (?)'',
[[31,32310512],[31,96438112]])
>
> that does not work, as it yields:
>
> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n-
31\n-
> 32310512\n'',''---\n- 31\n- 96438112\n''))
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
That does not work. It''s really hard to do that query if you
don''t use
tuples, you''d need to do one query for each possible value. Something
like
phones = []
[[31,32310512],[31,96438112]].each do |phone|
phones += Phone..where(ddd:phone[0], number:phone[1])
end
Either that or several joins. Being able to use certain features of your
DBMS can speed up code writting, make it more elegant and maintainable. At
the cost of being harder to switch between DBMS. But that''s already
hard
(since it''s almost impossible to use activerecord without using a few
SQL
statements here and there) and really has little practical use.
I''ve been thinking... If Phone.where(''(ddd, number) in
(?)'',
[[31,32310512],[31,96438112]]) generated the correct thing it would work
both in mysql and postgres, but it would not work on sqlite. However, it
doesn''t work in sqlite anyways. So, what''s the downside of
having that
generating the correct thing?
On Wednesday, May 22, 2013 4:47:45 PM UTC-3, Gary Weaver
wrote:>
> I was going to joke that you may as well add support for SPARQL, since for
> some reason it comes to mind when I think of tuples, and then found
> something that already supports SPARQL with ActiveModel:
> https://github.com/Swirrl/tripod
>
> Does this work?
>
> Phone.where(''(ddd, number) in ((?),(?))'', 31,32310512,
31,96438112)
> That looks cleaner to me. If you need to send in an array:
>
> Phone.where(''(ddd, number) in ((?),(?))'', *[31,32310512,
31,96438112])
>
> or your example:
>
> Phone.where(''(ddd, number) in ((?),(?))'', *
> [[31,32310512],[31,96438112]].flatten)
> and you could always construct the "(?),(?)" by counting the
flattened
> array to see how many ?''s you need.
>
>
> On Tuesday, May 21, 2013 12:12:56 PM UTC-4, Rafael Almeida wrote:
>>
>> I would very much be able to do this query with ActiveRecord:
>>
>> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in
>> ((31,32310512),(31,96438112))
>>
>> Unfortunately, ActiveRecord gives me no way to do so with a prepared
>> statement. The intuitive way to do it would be:
>>
>> Phone.where(''(ddd, number) in (?)'',
[[31,32310512],[31,96438112]])
>>
>> that does not work, as it yields:
>>
>> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in
(''---\n- 31\n-
>> 32310512\n'',''---\n- 31\n- 96438112\n''))
>>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
I don''t think it makes sense to add to change how the existing where in ActiveRecord::QueryMethods works for this case. Either you can construct one or more queries in SQL or you can''t. If you can, you can pass in parameters like I mentioned, which seem to answer your original question, so I''m really confused at why you don''t want to solve it that way. Since it is unclear how what you are suggesting would be backwards compatible and generally useful for all DBs that Rails supports internally, I think you should take this question to StackOverflow or Ruby on Rails: Talk group/list to get advice. And, I''d hope you wouldn''t have to keep adding queries like that. That doesn''t look very efficient. On Wednesday, May 22, 2013 6:19:52 PM UTC-4, Rafael Almeida wrote:> > That does not work. It''s really hard to do that query if you don''t use > tuples, you''d need to do one query for each possible value. Something like > > phones = [] > [[31,32310512],[31,96438112]].each do |phone| > phones += Phone..where(ddd:phone[0], number:phone[1]) > end > > Either that or several joins. Being able to use certain features of your > DBMS can speed up code writting, make it more elegant and maintainable. At > the cost of being harder to switch between DBMS. But that''s already hard > (since it''s almost impossible to use activerecord without using a few SQL > statements here and there) and really has little practical use. > > I''ve been thinking... If Phone.where(''(ddd, number) in (?)'', > [[31,32310512],[31,96438112]]) generated the correct thing it would work > both in mysql and postgres, but it would not work on sqlite. However, it > doesn''t work in sqlite anyways. So, what''s the downside of having that > generating the correct thing? > > On Wednesday, May 22, 2013 4:47:45 PM UTC-3, Gary Weaver wrote: >> >> I was going to joke that you may as well add support for SPARQL, since >> for some reason it comes to mind when I think of tuples, and then found >> something that already supports SPARQL with ActiveModel: >> https://github.com/Swirrl/tripod >> >> Does this work? >> >> Phone.where(''(ddd, number) in ((?),(?))'', 31,32310512, 31,96438112) >> That looks cleaner to me. If you need to send in an array: >> >> Phone.where(''(ddd, number) in ((?),(?))'', *[31,32310512, 31,96438112]) >> >> or your example: >> >> Phone.where(''(ddd, number) in ((?),(?))'', * >> [[31,32310512],[31,96438112]].flatten) >> and you could always construct the "(?),(?)" by counting the flattened >> array to see how many ?''s you need. >> >> >> On Tuesday, May 21, 2013 12:12:56 PM UTC-4, Rafael Almeida wrote: >>> >>> I would very much be able to do this query with ActiveRecord: >>> >>> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in >>> ((31,32310512),(31,96438112)) >>> >>> Unfortunately, ActiveRecord gives me no way to do so with a prepared >>> statement. The intuitive way to do it would be: >>> >>> Phone.where(''(ddd, number) in (?)'', [[31,32310512],[31,96438112]]) >>> >>> that does not work, as it yields: >>> >>> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 31\n- >>> 32310512\n'',''---\n- 31\n- 96438112\n'')) >>> >>-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Just noticed I messed up earlier because was copying/pasting and didn''t
see
that 31,32310512 were comma-separated values. I was looking at it as a
decimal- I need to get my eyes checked (or my European decimal notation
interpreter turned off).
How about:
a = [[1,2],[3,4]]
Phone.where("(ddd, number) in (#{([''(?,
?)'']*a.size).join('', '')})", *a)
That should work. Sorry, I should have gone back and looked more carefully.
But probably best to take this discussion off of the Rails core list.
On Thursday, May 23, 2013 12:30:15 AM UTC-4, Gary Weaver
wrote:>
> I don''t think it makes sense to add to change how the existing
where in
> ActiveRecord::QueryMethods works for this case.
>
> Either you can construct one or more queries in SQL or you can''t.
If you
> can, you can pass in parameters like I mentioned, which seem to answer your
> original question, so I''m really confused at why you
don''t want to solve it
> that way.
>
> Since it is unclear how what you are suggesting would be backwards
> compatible and generally useful for all DBs that Rails supports internally,
> I think you should take this question to StackOverflow or Ruby on Rails:
> Talk group/list to get advice.
>
> And, I''d hope you wouldn''t have to keep adding queries
like that. That
> doesn''t look very efficient.
>
> On Wednesday, May 22, 2013 6:19:52 PM UTC-4, Rafael Almeida wrote:
>>
>> That does not work. It''s really hard to do that query if you
don''t use
>> tuples, you''d need to do one query for each possible value.
Something like
>>
>> phones = []
>> [[31,32310512],[31,96438112]].each do |phone|
>> phones += Phone..where(ddd:phone[0], number:phone[1])
>> end
>>
>> Either that or several joins. Being able to use certain features of
your
>> DBMS can speed up code writting, make it more elegant and maintainable.
At
>> the cost of being harder to switch between DBMS. But that''s
already hard
>> (since it''s almost impossible to use activerecord without
using a few SQL
>> statements here and there) and really has little practical use.
>>
>> I''ve been thinking... If Phone.where(''(ddd, number)
in (?)'',
>> [[31,32310512],[31,96438112]]) generated the correct thing it would
work
>> both in mysql and postgres, but it would not work on sqlite. However,
it
>> doesn''t work in sqlite anyways. So, what''s the
downside of having that
>> generating the correct thing?
>>
>> On Wednesday, May 22, 2013 4:47:45 PM UTC-3, Gary Weaver wrote:
>>>
>>> I was going to joke that you may as well add support for SPARQL,
since
>>> for some reason it comes to mind when I think of tuples, and then
found
>>> something that already supports SPARQL with ActiveModel:
>>> https://github.com/Swirrl/tripod
>>>
>>> Does this work?
>>>
>>> Phone.where(''(ddd, number) in ((?),(?))'',
31,32310512, 31,96438112)
>>> That looks cleaner to me. If you need to send in an array:
>>>
>>> Phone.where(''(ddd, number) in ((?),(?))'',
*[31,32310512, 31,96438112])
>>>
>>> or your example:
>>>
>>> Phone.where(''(ddd, number) in ((?),(?))'', *
>>> [[31,32310512],[31,96438112]].flatten)
>>> and you could always construct the "(?),(?)" by counting
the flattened
>>> array to see how many ?''s you need.
>>>
>>>
>>> On Tuesday, May 21, 2013 12:12:56 PM UTC-4, Rafael Almeida wrote:
>>>>
>>>> I would very much be able to do this query with ActiveRecord:
>>>>
>>>> SELECT `phones`.* FROM `phones` WHERE (ddd, number) in
>>>> ((31,32310512),(31,96438112))
>>>>
>>>> Unfortunately, ActiveRecord gives me no way to do so with a
prepared
>>>> statement. The intuitive way to do it would be:
>>>>
>>>> Phone.where(''(ddd, number) in (?)'',
[[31,32310512],[31,96438112]])
>>>>
>>>> that does not work, as it yields:
>>>>
>>>> SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in
(''---\n- 31\n-
>>>> 32310512\n'',''---\n- 31\n-
96438112\n''))
>>>>
>>>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
On Thursday, May 23, 2013 1:30:15 AM UTC-3, Gary Weaver wrote:> > I don''t think it makes sense to add to change how the existing where in > ActiveRecord::QueryMethods works for this case. > > Either you can construct one or more queries in SQL or you can''t. If you > can, you can pass in parameters like I mentioned, which seem to answer your > original question, so I''m really confused at why you don''t want to solve it > that way. >The way you proposed doesn''t work 1.9.3p392 :004 > Phone.where(''(ddd, number) in ((?),(?))'', 31,32310512,31,96438112) ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (4 for 2) in: (ddd, number) in ((?),(?)) and even if it did it wouldn''t be very pretty to write. One of the good reasons to use the construct I propose is because the list could have variable number of phones. In order to support that with your solution one would have to write code for matching the number of (?) with the number of elements on the list. Since it is unclear how what you are suggesting would be backwards> compatible and generally useful for all DBs that Rails supports internally, > I think you should take this question to StackOverflow or Ruby on Rails: > Talk group/list to get advice. >I have already asked on StackOverflow a while back, but nobody knows the answer (http://stackoverflow.com/questions/10289522/multiple-or-conditions-in-activerecord). The change would be backwards compatible because it already breaks for every DBMS rails support. So, making it work for them would not break anyone''s code out there (unless it was already broken). It wouldn''t be useful to all DBs, because some wouldn''t be able to cope with tuples. However, the change is only an extension of how the prepared statement work, not a substitution of any kind of useful behavior. The way it expands lists of lists right now is not useful in any DBMS. If it became useful in a few, yet highly used, DBMS, then I would count that as a win. Maybe I have failed to be clear so far. This is phones schema: create_table "phones", :force => true do |t| t.string "ddd" t.string "number" t.integer "lawyer_id" t.datetime "created_at", :null => false t.datetime "updated_at", :null => false end add_index "phones", ["ddd", "number"], :name => "index_phones_on_ddd_and_number" add_index "phones", ["ddd"], :name => "index_phones_on_ddd" add_index "phones", ["lawyer_id"], :name => "index_phones_on_lawyer_id" add_index "phones", ["number"], :name => "index_phones_on_number" There''s a part of my system where the user gives me a list of phone numbers and I want to check if any of them are already in the database. ddd is how area code is called in Brazil. I hope I made things clearer now.> And, I''d hope you wouldn''t have to keep adding queries like that. That > doesn''t look very efficient. >As far as I know, that is the only solution to the problem right now that doesn''t involve not using string interpolation. That''s why I think this is an issue with how ActiveRecord works now. I also think the solution I proposed is reasonable, but, of course, I''m no rails developer or anything. I''d be happy if you tell me that there''s a way to do it right now that doesn''t involve string interpolation (and hopefully no string manipulation of any kind) or doing multiple queries. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
On Thursday, May 23, 2013 1:59:40 AM UTC-3, Gary Weaver wrote:> Just noticed I messed up earlier because was copying/pasting and didn''t > see that 31,32310512 were comma-separated values. I was looking at it as a > decimal- I need to get my eyes checked (or my European decimal notation > interpreter turned off). > > How about: > > a = [[1,2],[3,4]] > Phone.where("(ddd, number) in (#{([''(?, ?)'']*a.size).join('', '')})", *a) > > That should work. Sorry, I should have gone back and looked more > carefully. But probably best to take this discussion off of the Rails core > list.Why take it off rails core? Isn''t the discussion whether where(''(?)'', [[1,2],[3,4]]) should expand to ((1,2),(3,4)) or not be here? Just like I rather write Phone.where(''number in (?)'', [32214422,5553221]), than num=[32214422,5553221]; Phone.where("number in (#{([''?'']*num.size).join('','')})", *num). I''d rather write Phone.where(''(ddd,number) in (?)'', [[1,2],[3,4]]) rather than a = [[1,2],[3,4]];Phone.where("(ddd, number) in (#{([''(?, ?)'']*a.size).join('', '')})", *a.flatten). Specially when Phone.where("(ddd,number) in (?)", [[1,2],[3,4]]) generates SELECT `phones`.* FROM `phones` WHERE ((ddd, number) in (''---\n- 1\n- 2\n'',''---\n- 3\n- 4\n'')) which is not useful at all. I suggest that Phone.where("(?)", [[1,2],[3,4]]) at least throws an exception rather than generating ''(---\n- 1\n- 2\n'',''---\n- 3\n- 4\n)''. Otherwise, if you''re adding random characters, at least add the random characters ''('', '')'' and '','' in the following maner: ''(1,2),(3,4)'' :P. Unless those ''---\n-'' are not random. Anyway, I believe everybody now already understands what I''m suggesting and why. It''s, of course, up to you to evaluate if any of this is an worthy feature or not. Cheers, Rafael -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
I was actually considering writing a gem in my spare time that patches ActiveRecord::QueryMethods to provide the functionality you want, because of the following: While it is obvious to you what arrays should be interpreted that way, that changes the current behavior to produce SQL that is not compliant with every database that Rails supports, so until it is supported in SQLite, what you want will not be part of Rails core, afaik. But, I get the feeling you are much more able than I am, since you seem to know what is best for ActiveRecord, so have at it yourself. :) On Thursday, May 23, 2013 3:54:39 AM UTC-4, Rafael Almeida wrote:> > On Thursday, May 23, 2013 1:59:40 AM UTC-3, Gary Weaver wrote: > >> Just noticed I messed up earlier because was copying/pasting and didn''t >> see that 31,32310512 were comma-separated values. I was looking at it as a >> decimal- I need to get my eyes checked (or my European decimal notation >> interpreter turned off). >> >> How about: >> >> a = [[1,2],[3,4]] >> Phone.where("(ddd, number) in (#{([''(?, ?)'']*a.size).join('', '')})", *a) >> >> That should work. Sorry, I should have gone back and looked more >> carefully. But probably best to take this discussion off of the Rails core >> list. > > > Why take it off rails core? Isn''t the discussion whether where(''(?)'', > [[1,2],[3,4]]) should expand to ((1,2),(3,4)) or not be here? > > Just like I rather write Phone.where(''number in (?)'', [32214422,5553221]), > than num=[32214422,5553221]; Phone.where("number in > (#{([''?'']*num.size).join('','')})", *num). I''d rather write > Phone.where(''(ddd,number) in (?)'', [[1,2],[3,4]]) rather than a = > [[1,2],[3,4]];Phone.where("(ddd, number) in (#{([''(?, ?)'']*a.size).join('', > '')})", *a.flatten). Specially when Phone.where("(ddd,number) in (?)", > [[1,2],[3,4]]) generates SELECT `phones`.* FROM `phones` WHERE ((ddd, > number) in (''---\n- 1\n- 2\n'',''---\n- 3\n- 4\n'')) which is not useful at > all. > > I suggest that Phone.where("(?)", [[1,2],[3,4]]) at least throws an > exception rather than generating ''(---\n- 1\n- 2\n'',''---\n- 3\n- 4\n)''. > Otherwise, if you''re adding random characters, at least add the random > characters ''('', '')'' and '','' in the following maner: ''(1,2),(3,4)'' :P. > Unless those ''---\n-'' are not random. > > Anyway, I believe everybody now already understands what I''m suggesting > and why. It''s, of course, up to you to evaluate if any of this is an worthy > feature or not. > > Cheers, > Rafael >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
On Thursday, May 23, 2013 3:54:39 AM UTC-4, Rafael Almeida wrote:> > ... at least throws an exception rather than generating ''(---\n- 1\n- > 2\n'',''---\n- 3\n- 4\n)''. Otherwise, if you''re adding random characters ... >They are not random characters. mkdir tmp cd tmp git clone git://github.com/rails/rails.git grep -r \\-\\-\\- * Notice things like: execute <<_SQL CREATE TABLE defaults ( id serial primary key, modified_date date default CURRENT_DATE, modified_date_function date default now(), fixed_date date default ''2004-01-01'', modified_time timestamp default CURRENT_TIMESTAMP, modified_time_function timestamp default now(), fixed_time timestamp default ''2004-01-01 00:00:00.000000-00'', char1 char(1) default ''Y'', char2 character varying(50) default ''a varchar field'', char3 text default ''a text field'', positive_integer integer default 1, negative_integer integer default -1, bigint_default bigint default 0::bigint, decimal_number decimal(3,2) default 2.78, multiline_default text DEFAULT ''--- [] ''::text ); _SQL See that multiline_default? Then a google search for: rails multiline_default shows this commit: https://github.com/rails/rails/commit/4eaa8ba5bec9001e97349e7dfd565c7215085834 Maybe that has something to do with it? Maybe shouldn''t be raising an error because some might be using that? I don''t know. Maybe you could add something so that this is supported: MyModel.where([:a, :b] => [[1,2],[3,4]]) MyModel.where([:a, :nice, :triple] => [[1,2,3],[4,5,6]]) MyModel.where([:any, :number, :of, :members] => [[1,2,3,4],[5,6,7,8]]) but if you did: MyModel.where([:a, :b] => [[1,2],[3,[4,5]]]) maybe the [4,5] would render as the wierd multiline string version of the array for that specific part of the tuple''s second value. I''m not sure whether that would break anything, but if it did then could just have a different method name like where_tuple(...) to do that. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
On Thursday, May 23, 2013 9:45:20 AM UTC-4, Gary Weaver wrote:> See that multiline_default? Then a google search for: rails > multiline_default shows this commit: > > https://github.com/rails/rails/commit/4eaa8ba5bec9001e97349e7dfd565c7215085834 >It looks like that commit would allow sending arrays in as arrays (e.g. http://www.postgresql.org/docs/9.2/static/arrays.html), btw, not tuples. Unless, I''m misreading it. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Ok I think I finally understand, maybe.
Single value tuple wheres are/were supported:
http://stackoverflow.com/questions/15750234/ruby-activerecord-and-sql-tuple-support
e.g. Person.where("(name, address) IN ((?))",
[''John'', ''123 Main St''])
And you are right that one would expect that if that is supported then
because of:
http://guides.rubyonrails.org/active_record_querying.html#subset-conditions
Client.where(:orders_count => [1,3,5]) # => "SELECT * FROM clients
WHERE
(clients.orders_count IN (1,3,5))"
That you would expect this to work:
Person.where("(name, address) IN ((?))", [[''John'',
''123 Main St''],[''Mary'',
''321 Easy St'']])
But, a where value that is an array could also be an array type in the
database, per Jeremy adding multiline string support.
So I think the question is really about "where" being a little too
ambiguous about the intent.
If tuples are already supported in AR (which it looks like they are), then
even there is not first-class tuple support in every database, I''m
actually
leaning towards maybe a where_tuple or similar method being a better way
for ActiveRecord to continue formally supporting tuples in a more intuitive
way, so that:
Person.where_tuple([:name, :address] => [[''John'',
''123 Main St''],[''Mary'',
''321 Easy St'']])
and:
Person.where_tuple("(name, address) IN ((?))",
[[''John'', ''123 Main
St''],[''Mary'', ''321 Easy St'']])
would work. But for the short-term, I think doing that in a gem or as a
patch in an initializer might be the best solution.
On Thursday, May 23, 2013 3:54:39 AM UTC-4, Rafael Almeida
wrote:>
> On Thursday, May 23, 2013 1:59:40 AM UTC-3, Gary Weaver wrote:
>
>> Just noticed I messed up earlier because was copying/pasting and
didn''t
>> see that 31,32310512 were comma-separated values. I was looking at it
as a
>> decimal- I need to get my eyes checked (or my European decimal notation
>> interpreter turned off).
>>
>> How about:
>>
>> a = [[1,2],[3,4]]
>> Phone.where("(ddd, number) in (#{([''(?,
?)'']*a.size).join('', '')})", *a)
>>
>> That should work. Sorry, I should have gone back and looked more
>> carefully. But probably best to take this discussion off of the Rails
core
>> list.
>
>
> Why take it off rails core? Isn''t the discussion whether
where(''(?)'',
> [[1,2],[3,4]]) should expand to ((1,2),(3,4)) or not be here?
>
> Just like I rather write Phone.where(''number in (?)'',
[32214422,5553221]),
> than num=[32214422,5553221]; Phone.where("number in
> (#{([''?'']*num.size).join('','')})",
*num). I''d rather write
> Phone.where(''(ddd,number) in (?)'', [[1,2],[3,4]]) rather
than a =
> [[1,2],[3,4]];Phone.where("(ddd, number) in (#{([''(?,
?)'']*a.size).join('',
> '')})", *a.flatten). Specially when
Phone.where("(ddd,number) in (?)",
> [[1,2],[3,4]]) generates SELECT `phones`.* FROM `phones` WHERE ((ddd,
> number) in (''---\n- 1\n- 2\n'',''---\n- 3\n-
4\n'')) which is not useful at
> all.
>
> I suggest that Phone.where("(?)", [[1,2],[3,4]]) at least throws
an
> exception rather than generating ''(---\n- 1\n-
2\n'',''---\n- 3\n- 4\n)''.
> Otherwise, if you''re adding random characters, at least add the
random
> characters ''('', '')'' and
'','' in the following maner: ''(1,2),(3,4)''
:P.
> Unless those ''---\n-'' are not random.
>
> Anyway, I believe everybody now already understands what I''m
suggesting
> and why. It''s, of course, up to you to evaluate if any of this is
an worthy
> feature or not.
>
> Cheers,
> Rafael
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
On Thursday, May 23, 2013 9:53:20 AM UTC-4, Gary Weaver wrote:> > On Thursday, May 23, 2013 9:45:20 AM UTC-4, Gary Weaver wrote: > >> See that multiline_default? Then a google search for: rails >> multiline_default shows this commit: >> >> https://github.com/rails/rails/commit/4eaa8ba5bec9001e97349e7dfd565c7215085834 >> > > It looks like that commit would allow sending arrays in as arrays (e.g. > http://www.postgresql.org/docs/9.2/static/arrays.html), btw, not tuples. > Unless, I''m misreading it. >To clarify, Rails is storing the array as YAML. Nothing to do with PG arrays. Just had arrays on the brain at the time. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscribe@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core?hl=en. For more options, visit https://groups.google.com/groups/opt_out.