Join the Stack Overflow Community
Stack Overflow is a community of 6.9 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite
1

What I wrong with me or this query?

I have a Shop model with an opening days column, which is an array of integers (something you can do with Postgres):

days= [1,1,1,1,0,0,0]

When I query:

shops = Shop.where('days[0] = 1')

I get an empty ActiveRecord Relation.

=> #<ActiveRecord::Relation []>

When I take a shop with this kind of array…

shop = Shop.first
=> #<Shop id: 215, days: [1, 1, 1, 1, 0, 0, 0],…

If I do

shop.days[0]

I get

=> 1

I really don't get it.

share|improve this question
    
How did you define the days column in PG? – Simone Carletti Sep 20 '15 at 10:07
    
Like this : t.integer :days, array: true, default: [1,1,1,1,1,1,1] – Sebabouche Sep 20 '15 at 10:10
    
And this is how it "looks" in my database column : {1,1,1,1,1,1,1}. And the title column is days integer[ ] – Sebabouche Sep 20 '15 at 10:12
    
What is the real query created and executed by Rails? Check the output in the console and try it on the PG console. Does it work? – Simone Carletti Sep 20 '15 at 10:15
    
The query is : SELECT "shops".* FROM "shops" WHERE (days[0] = 1) and the answer is => #<ActiveRecord::Relation []>. I don't know how to use the PG console so I'm gonna check it out ;) – Sebabouche Sep 20 '15 at 10:21
up vote 3 down vote accepted

By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

— Source

It's just your example. Your index is out of bounds, so it doesn't match any records, days[0] is NULL. Everywhere. Fire up rails db and figure:

SELECT * FROM shops WHERE days[0] IS NULL;

But what's with that "by default"? Is it possible to define array bounds on schema level so this never becomes an issue?

Well... it's Rails' fault, I'm afraid. If it's even considered an "issue" at all. In order for an array to be zero-indexed it should be saved as such in SQL. I tried that in SQL, it works:

INSERT INTO shops
        (days,           created_at,        updated_at)
  values('[0:2]={1, 1, 0}', current_timestamp, current_timestamp);

Unfortunately, Rails loses bounds for some reason:

 Shop.create(days: '[0:3]={6, 7, 8, 9}')
 > INSERT ... [["days", "{6,7,8,9}"], ...]
share|improve this answer
    
Ah, that would explain why this didn't happened yesterday ^^. Time.zone.now.wday = 0 today !!! – Sebabouche Sep 20 '15 at 10:27
    
I'm researching if it's possible to set the lower bound to 0, since this is inconsistent with most of the other tools Rails developers use. – D-side Sep 20 '15 at 10:29
    
Thank you because my code was pretty wrong by one day. If I had enough reputation, I grant you the one I owe you. – Sebabouche Sep 20 '15 at 10:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.