Pattern findings using “find” in Rails

Rails
#1

Hi,

This is my current code

@descriptions = TableName.find(:first, :conditions=> [“table_id = ?”,
table_name.table_id], :order => ‘author_year’)

author_year column contains data of

kannan 1845

kohlun 1976

palani 1956

Using above code, it gives result with order of author_year based on
author
names. I need to order the query ascendingly based on the year which
presents in author_year. and I wish to print the oldest data based on
the
year not ordered by author name. Kindly give me some suggestion on this
issue.

–
With Regards,
Palani Kannan. K,

#2

Refactor the DB to have those fields available separately. You have two
distinct pieces of data munged together in one field.

#3

Hi Ar Chron,

Thank you for reply

On 25 October 2010 14:55, Ar Chron [email protected] wrote:

Refactor the DB to have those fields available separately. You have two
distinct pieces of data munged together in one field.

I shouldn’t edit or modify the data in database. The data has not
munged.
The default data field is created with two pieces. In general author
referencing, the data will be included with year and author name. Author
name and year are the two pieces in author_year column. Kindly tell me
if
functions available to sort SQL query output with second piece only as
sorting element.

–
With Regards,

Palani Kannan. K,

#4

On Oct 25, 3:06pm, PalaniKannan K [email protected] wrote:

On 25 October 2010 14:55, Ar Chron [email protected] wrote:
I shouldn’t edit or modify the data in database. The data has not munged.
The default data field is created with two pieces. In general author
referencing, the data will be included with year and author name. Author
name and year are the two pieces in author_year column. Kindly tell me if
functions available to sort SQL query output with second piece only as
sorting element.

If you really want to ignore what others have said the :order options
takes an sql fragment. SQL has some string manipulation functions so
you can probably come up with a way of extracting the year from that
column, but such a query wouldn’t be able to use any indexes to help
with the ordering so you might end up with something slow as well as
something yucky.

Fred

#5

PalaniKannan K wrote in post #956892:

Hi Ar Chron,

Thank you for reply

On 25 October 2010 14:55, Ar Chron [email protected] wrote:

Refactor the DB to have those fields available separately. You have two
distinct pieces of data munged together in one field.

I shouldn’t edit or modify the data in database.

No, but you should change the schema.

The data has not
munged.

Yes it is.

The default data field is created with two pieces.

Right. That’s a bad design. You should change it. One field should
contain only one piece of data.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#6

Hi Frederick,

2010/10/25 Frederick C. [email protected]

If you really want to ignore what others have said the :order options
takes an sql fragment. SQL has some string manipulation functions so
you can probably come up with a way of extracting the year from that
column, but such a query wouldn’t be able to use any indexes to help
with the ordering so you might end up with something slow as well as
something yucky.

I tried
def author_yr
“#{self.author_year.last(4)}”
end.

But it shows “Undefined local variable or method ‘author_yr’”

when i use author_yr in find by

“:order => [“author_year like = ?”, author_yr]”. Please suggest me some
idea
in ordering.

Fred

–
With Regards,

Palani Kannan. K,

#7

Ar Chron wrote in post #957266:

PalaniKannan K wrote in post #957235:

I tried
def author_yr
“#{self.author_year.last(4)}”
end.

But it shows “Undefined local variable or method ‘author_yr’”

when i use author_yr in find by

“:order => [“author_year like = ?”, author_yr]”. Please suggest me some
idea
in ordering.

Palani Kannan. K,

Impossible.

You’re defining author_yr in a ruby model, but expecting the DATABASE to
know what that is. Doesn’t work that way.

Where is the OP doing that?

What he is doing instead that is wrong is using a Rails-style conditions
array in an ORDER BY clause. That doesn’t work that way.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#8

PalaniKannan K wrote in post #957235:

I tried
def author_yr
“#{self.author_year.last(4)}”
end.

But it shows “Undefined local variable or method ‘author_yr’”

when i use author_yr in find by

“:order => [“author_year like = ?”, author_yr]”. Please suggest me some
idea
in ordering.

Palani Kannan. K,

Impossible.

You’re defining author_yr in a ruby model, but expecting the DATABASE to
know what that is. Doesn’t work that way.

As Fred mentioned, you need to use a database function that looks at the
“author_year” field (the last 4 characters) to order upon.

Look in the manuals for whatever DB engine you’re using for string
functions. That’s what goes in the :order => []

#9

“author_year” field (the last 4 characters) to order upon.

I got your point. I will look for last 4 character to order up in
database
function. thank you for suggestion.

To post to this group, send email to [email protected]
To unsubscribe from this group, send email to

[email protected][email protected]

.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–
With Regards,

Palani Kannan. K,
Doctoral Researcher,
Microbiology Department,
German Collection Center for Microorganisms and Cell Cultures,
Helmholtz-HZI Infectious diseases Research Campus,
38124, Braunschweig,
Germany.