[SOLVED] How to fetch the last child record group by parent ID with matched conditions

How to fetch the last child record group by parent ID with matched conditions

I am using meta_search gem in my projects and have users and user_details tables.

users table should have unchangeable informations such as name, sex, blood group, date of birth..etc

user_details have address, mobile number...etc

user.rb

has_many :user_details

user_detail.rb

belongs_to :user

I am treating every updated user_details record as new record. I am currently displaying the last record of user_details as user's current record

user_details table looks like

id name       gender   phone        user_id
1. xxx        m        99389989     1
2  xxx        f        3344444      1
3  xxx        m        323434       1
4  xxx yy     f        3324324      2
5  xxx yyy    f        332423       2

Question: When I am searching with key word, How to fetch the last user_details record for every user with matched conditions.

Example: if I search name_starts_with 'xxx' then it is fetching the ID 1 to 5. But I want to fetch only ID 3 and 5

Thanks in Advance

Solution

Finally I have fixed using mysql's MAX method

scope :last_record_in_each_group, lambda { |name|
  cond = "user_details.id in (select max(user_details.id) from user_details group by user_details.user_id)"
  cond << " and upper((CONCAT(first_name,' ', middle_name,' ', last_name))) like ?"
  {:conditions => [cond, "%#{name.upcase}%"]}
}

In view

<%= search.text_field :last_record_in_each_group, :placeholder => "User Name" %>