How to get id number from different locale in model

3671 views mysql
-1

I have one model (category.rb) in my rails application configured as below:

id,name,locale

1,Sociability,en

2,Prudence and Delicacy,en

3,???,zh-TW

4,"???????",zh-TW

I would like to know the initial ID for different locale, for example, the initial ID for :en will be "1" and ID for :"zh-TW" will be "3". Instead of hard-coded to define the initial ID in application, is there any gem or method to identify the initial ID? So that in future, when I add new record as 5, Sociabilidad, es, I can get the ID correctly without hard-coded?

Please advise. Thank you.

answered question

Are you using postgresql?

Thanks for promptly response. No, I use "MySQL". Will it make difference for postgresql?

No some DB specific functions sometime, so it is better to know before I suggest something. tag the question with mysql also

Got it. Thanks!

@strawberry it was a dup in SQL, but not Rails. So I reopened it and shown the OP how to write it in Rails AR way.

1 Answer

2

I'd write this as below using AR:

class Category < ApplicationRecord
  scope :first_in_locale, ->() {
    joins(<<-SQL)
      JOIN (SELECT locale, min(id) as id from categories group by locale) b on b.id = categories.id
    SQL
    .select('categories.*')
    .order('categories.id')
  }
end

In Rails console:

2.5.1 :010 > pp Category.first_in_locale
  Category Load (0.9ms)  SELECT categories.* FROM "categories" JOIN (SELECT locale, min(id) as id from categories group by locale) b on b.id = categories.id ORDER BY categories.id
[#<Category:0x00007fc597e3db68
  id: 1,
  name: "Sociability",
  locale: "en",
  created_at: Sun, 16 Sep 2018 07:47:34 UTC +00:00,
  updated_at: Sun, 16 Sep 2018 07:47:34 UTC +00:00>,
 #<Category:0x00007fc597e3d3e8
  id: 3,
  name: "???",
  locale: "zh-TW",
  created_at: Sun, 16 Sep 2018 07:47:34 UTC +00:00,
  updated_at: Sun, 16 Sep 2018 07:47:34 UTC +00:00>]

Here is the SQL answer I wrapped in ActiveRecord way.

posted this

Have an answer?

JD

Please login first before posting an answer.