pattern findings using “find” in Rails

rubyonrails-talk
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.

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

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.

4

PalaniKannan K wrote in post #956892:

Hi Ar Chron,

Thank you for reply

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,

5

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

6

Hi Frederick,

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.

7

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 =>

8

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,

9

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. I got your point. I will look for last 4 character to order up in database function. thank you for suggestion.