ActiveRecord and sql tuple support

[Rafael Almeida]

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'))

[Ryan Bigg]

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?

--
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.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Rafael Almeida]

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?

[Anuj Dutta]

I did write something trivial 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

--
Anuj DUTTA

[Gary Weaver]

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.

[Rafael Almeida]

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?

[Gary Weaver]

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.

[Gary Weaver]

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.

[Rafael Almeida]

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.

[Rafael Almeida]

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

[Gary Weaver]

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. :)

[Gary Weaver]

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.

[Gary Weaver]

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.

[Gary Weaver]

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:

[Gary Weaver]

To clarify, Rails is storing the array as YAML. Nothing to do with PG arrays. Just had arrays on the brain at the time.