Edoardo,
1. you may do well to store the id as a string
you can index that, and many databases will use an index with a like clause
(given the % is on the right hand of the string)
only way I know how to create this is using:
def after_create
if id_string.blank?
id_string=id.to_s
save
end
end
2. it may be too late in the game, but there is this concept of internal and
external ids
the internal id is the integer you use in your join tables.
the external id is the string/number you use to refer to this record to other
people.
the external id is the one in the url, and possibly the only one the customer
knows.
example: first initial, last 4 letters of their name and a sequence ( ezari005 )
the url would use that for /people/ezari005
Or you could use the user chosen id for the url. twitter does this.
if you make the field fixed length and non-nullable, you could get some
optimization for the indexing. (that is assuming a system generated index)
Accessing the record may be 1 extra lookup or may not.
def to_param is your friend, but there is also a great plugin out there they
mentioned on rails envy a while back (called the ruby show now)
If you already have the ids released into the wild, may be no turning back.
Best of luck,
--Keenan
On May 25, 2010, at 3:09 AM, edoardoz wrote:
> Thank for your answer.
>
> The business case is to search documents by incomplete (user
> submitted)
> id.
>
> Due this case is managed out-of-the box by mysql, and we have many
> cases like this,
> it's possible to enable implicit casting even in heroku's postgres?
>
> Regards.
>
> On May 24, 11:42 pm, Jonathan Dance <j...@norbauer.com> wrote:
>> In Postgres, you can only do string searches on string columns. Your options
>> are thusly:
>>
>> 1. Stop doing this. Do you really want to be searching an integer like a
>> string?
>> 2. Convert ID to a string column. You shouldn't do this either, unless "id"
>> was just an example.
>> 3. Tell Postgres to cast it to a string before running the LIKE clause. This
>> should work in MySQL as well - MySQL was doing it implicitly, but you must
>> do it explicitly in Postgres:
>> :conditions => "CAST(id AS char) LIKE '80%'"
>>
>> You shouldn't do this either, as it's a slow operation, although Postgres
>> (but not MySQL) does allow indexing on derived columns. Note that I've set
>> the character limit to 8 on the string cast here to limit the size of the
>> index:
>>
>> CREATE INDEX id_string_idx ON documents ((CAST(id AS char(8))));
>>
>> --wuputah
>>
>>
>>
>>
>>
>> On Mon, May 24, 2010 at 1:42 PM, edoardoz <edoardo.z...@gmail.com> wrote:
>>> Something that work perfectly in my app with MySQL:
>>
>>> Document.find(:all,:conditions => ["id LIKE ?","80%"])
>>
>>> isn't working with PostgreSQL 8.3 in Heroku:
>>
>>> ActiveRecord::StatementInvalid: PGError: ERROR: operator does not
>>> exist: integer ~~ unknown
>>> LINE 1: SELECT * FROM "documents" WHERE (id LIKE E'D%')
>>> ^
>>> HINT: No operator matches the given name and argument type(s). You
>>> might need to add explicit type casts.
>>> : SELECT * FROM "documents" WHERE (id LIKE E'D%')
>>
>>> Something happened to type casting: what to do?
>>
>>> Regards.
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Heroku" group.
>>> To post to this group, send email to her...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> heroku+unsubscr...@googlegroups.com<heroku%2bunsubscr...@googlegroups.com>
>>> .
>>> For more options, visit this group at
>>> http://groups.google.com/group/heroku?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Heroku" group.
>> To post to this group, send email to her...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> heroku+unsubscr...@googlegroups.com.
>> For more options, visit this group
>> athttp://groups.google.com/group/heroku?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Heroku" group.
> To post to this group, send email to her...@googlegroups.com.
> To unsubscribe from this group, send email to
> heroku+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/heroku?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Heroku" group.
To post to this group, send email to her...@googlegroups.com.
To unsubscribe from this group, send email to
heroku+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/heroku?hl=en.
