Cross referencing tables in Ruby on Rails

Question

schema db

I have an ads table which has 1:n relationship to an ad_address and an ad_copies tables. Then there are the categories and the subcategories tables where the category has 1:n relationship with the subcategory table and the subcategory table has a 1:n relationship with the ads table.

So the subcategory table has a reference field called id_categories and the ads table has a reference field id_subcategories.

The ads_address and ads_copy tables both have an ad_id field.

Now I've been busy trying to get data from a json file (a previous database) into a new rails app. I've discovered that the schema of the this previous database contained a separate table which has all the reference fields together, i.e., the id_categories, id_subcategories and ad_id fields.

create_table "rel_anuncio", primary_key: ["id_anuncio", "id_categoria", "id_subcategoria"], force: :cascade, options: "ENGINE=MyISAM DEFAULT CHARSET=utf8" do |t|
  t.integer "id_anuncio", null: false
  t.integer "id_categoria", null: false
  t.integer "id_subcategoria", null: false
  t.integer "orden", null: false
end

Here's how I seed my ad records (the table names of the json file are Spanish):

if row['name'] == "categorias"

row["data"].each do |record|
  @cat = Category.create!(
    user_id: user.id,
    name: record["nombre"]
  )
end

elsif row['name'] == "subcategorias"

row["data"].each do |record|
  @subcat = Subcategory.create!(
    category_id: @cat.id,
    name: record["nombre"]
    )
end

row["data"].each do |record|
  @ad = Ad.create!(
    user_id: user.id,
    subcategory_id: @subcat.id,
    [..]
    )
end

I get an a "NoMethodError: undefined method 'id' for nil:NilClass" for the line "subcategory_id: @subcat.id", because there's no subcategory_id to link to the ad record I realized, because the subcategory_id is in a different table.

Now I know there must be a way to cross reference these id's within the hash so that you can see which id numbers belongs to which record. For example, if you have id_subcategory: 3, then this must belong to the ad record with name "Wok Restaurant" with id: 4.

I want to seed the correct category and subcategory to my ad records in my rails app.

More in general I'd like to know how someone would handle importing a json file of a database with a different schema than your own database in a railsapp.

Here's sample of the json file (only first line of each table):

[
  {"type":"header","version":"4.7.9","comment":"Export to JSON plugin for PHPMyAdmin"},
  {"type":"database","name":"qqx486"},
  {"type":"table","name":"anuncios","database":"qqx486","data":
    [
      {"id":"1","empresa":"Wok Bufet Gran Oriente","tel":"971 315 964","fax_principal":"","movil_principal":"","email_principal":"","web":"","facebook":"","horario_v_principal":"","horario_i_principal":"Abierto mediod\u00eda y noche","direccion_principal":"C\/ Jos\u00e9 Zornoza Bernabeu, 15, L2","poblacion_principal":"EIVISSA","activo":"no","tam_anuncio":"completa","twitter":"","link":"no","general":"no","isla":"ibiza","subtitulo":"","comentario":"","modificacion":"0000-00-00 00:00:00","promo1":"0","promo2":"0","instagram":"","tel2":"","tel3":"","tel4":"","movil2":"","movil3":"","movil4":""},
    ]
  }
  ,{"type":"table","name":"anuncios_direcciones","database":"qqx486","data":
    [
      {"id":"157","id_anuncio":"1","direccion":"C\/ Sant Jaume, 114","poblacion":"SANTA EUL\u00c0RIA","telefono":" 971 336 274","fax":"","movil":"","email":"","horario_i":"","horario_v":""},
    ]
  }
  ,{"type":"table","name":"anuncios_gps_a","database":"qqx486","data":
    [
      {"id":"1","id_anuncio":"22","lat":"38.9082100","lon":"1.4289690","accuracy":"10"},
    ]
  }
  ,{"type":"table","name":"anuncios_gps_b","database":"qqx486","data":
    [
      {"id_anuncio_direcciones":"459","id_anuncio":"1068","lat":"0.0000000","lon":"0.0000000","acc":"0"},
    ]
  }
  ,{"type":"table","name":"anuncios_textos","database":"qqx486","data":
    [
      {"id_anuncio":"1","id_idioma":"1","descripcion":""},
    ]
  }
  ,{"type":"table","name":"caracteristicas","database":"qqx486","data":
    [
      {"id":"1","nombre":"Jard\u00edn"},
    ]
  }
  ,{"type":"table","name":"caracteristicas_textos","database":"qqx486","data":
    [
      {"id_caracteristica":"1","id_idioma":"1","titulo":"Jard\u00edn"},
    ]
  }
  ,{"type":"table","name":"categorias","database":"qqx486","data":
    [
      {"id":"1","nombre":"RESTAURANTES Y ALIMENTACI\u00d3N","color":"","activo":"si","bdd":"guias","orden":"1","promoI":"0","promoF":"0","islas":"3"},
    ]
  }
  ,{"type":"table","name":"categorias_porsi","database":"qqx486","data":
    [
      {"id":"1","nombre":"RESTAURANTES Y ALIMENTACI\u00d3N","color":"","activo":"si","bdd":"guias","orden":"1","promoI":"0","promoF":"0"},
    ]
  }
  ,{"type":"table","name":"categorias_textos","database":"qqx486","data":
    [
      {"id_categoria":"1","id_idioma":"1","titulo":"RESTAURANTES Y ALIMENTACI\u00d3N","clave":""},
    ]
  }
  ,{"type":"table","name":"enlaces","database":"qqx486","data":
    [
      {"id":"1","nombre":"Digital Grafic Ibiza","web":"http:\/\/www.bestof.org"}
    ]
  }
  ,{"type":"table","name":"enlaces_textos","database":"qqx486","data":
    [
      {"id_enlace":"1","id_idioma":"1","descripcion":"Empresa en Ibiza"},
    ]
  }
  ,{"type":"table","name":"idiomas","database":"qqx486","data":
    [
      {"id":"1","nombre":"Espa\u00f1ol","self":"es","color":"F00"},
    ]
  }
  ,{"type":"table","name":"inmuebles","database":"qqx486","data":
    [
      {"id":"1","ref":"Roca Llisa","id_inmobiliaria":"91","tipo":"piso","estado":"","zona":"Santa Eulalia","sup_vivienda":"0","sup_parcela":"0","sup_terrazas":"0","plantas":"0","dormitorios":"3","banyos":"2","piscina":"no","parking":"no","entorno":"","precio":"380000","activo":"si"},
    ]
  }
  ,{"type":"table","name":"inmuebles_textos","database":"qqx486","data":
    [
      {"id_inmueble":"1","id_idioma":"1","titulo":"Roca Llisa","descripcion":"Apartamento d\u00faplex, cocina, sal\u00f3n\/comedor, 2 terrazas, preciosas vistas al mar, piscina y paddle tenis comunitarios."},
    ]
  }
  ,{"type":"table","name":"playas","database":"qqx486","data":
    [
      {"id":"1","nombre":"PLATJA DE TALAMANCA","longitud":"900","orientacion":"SE","google":"https:\/\/www.google.es\/maps\/place\/Cala+Talamanca\/@38.9132677,1.4565971,16z\/data=!4m2!3m1!1s0x1299414743b6e207:0xe628e2f006419c0d","cnauticas":"38\u00b055\u201900.8\u201dN 1\u00b027\u201916.8\u201dE","municipio":"Eivissa","activo":"si","orden":"0"},
    ]
  }
  ,{"type":"table","name":"playas_textos","database":"qqx486","data":
    [
      {"id_playa":"1","id_idioma":"1","breve":"Playa de arena fina a pocos minutos a pie de Marina Botafoch y a 1,8 km. del centro de la ciudad de Eivissa.","descripcion":"Se trata de una bah\u00eda cerrada, poco profunda, ideal para el ba\u00f1o sin ning\u00fan peligro y muy bien protegida por los vientos. Gran variedad en restaurantes, bares y chiringuitos, incluso algunos est\u00e1n abiertos todo el a\u00f1o. Existe una pasarela de madera a lo largo de casi toda la playa. Se realizan cursos para iniciarse en deportes n\u00e1uticos como el catamar\u00e1n, kayak y windsurf.","servicios":"Restaurantes y chiringuitos \u2013 Hoteles \u2013 Alquiler de tumbonas y sombrillas \u2013 Deportes acu\u00e1ticos \u2013 Duchas y WC p\u00fablicos \u2013 Acceso minusv\u00e1lidos \u2013 Parking gratis \u2013 Transporte p\u00fablico.","llegar":"En coche, en autob\u00fas de l\u00ednea regular o en una barquita que va del puerto de Eivissa hasta Botafoch, y de ah\u00ed puede ir andando llegando en pocos minutos."},
    ]
  }
  ,{"type":"table","name":"promo","database":"qqx486","data":
    [
      {"id":"1","idioma":"1","empresa_id":"943","titulo":"10% Descuento","tituloCR":"8","tituloCG":"8","tituloCB":"8","tituloFS":"20","texto1":"7 horas de fiesta y mucho m\u00e1s","texto1CR":"255","texto1CG":"255","texto1CB":"255","texto1FS":"20","fondo1CR":"255","fondo1CG":"43","fondo1CB":"227","fondo1CA":"90","texto2":"","foto1":"","foto2":"","code":"C\u00f3digo: Isla Blanca","tiempo1":"0000-00-00 00:00:00","tiempo2":"0000-00-00 00:00:00","activo":"2","status":"1","orden":"1"},
    ]
  }
  ,{"type":"table","name":"rel_anuncio","database":"qqx486","data":
    [
      {"id_anuncio":"814","id_categoria":"7","id_subcategoria":"81","orden":"37"},
    ]
  }
  ,{"type":"table","name":"rel_guias","database":"qqx486","data":
    [
      {"id_anuncio":"7","id_guia":"1"},
    ]
  }
  ,{"type":"table","name":"rel_inmuebles","database":"qqx486","data":
    [
      {"id_inmueble":"1","id_caracteristica":"2"},
    ]
  }
  ,{"type":"table","name":"subcategorias","database":"qqx486","data":
    [
      {"id":"1","id_categoria":"1","nombre":"Restaurantes","color":"","activo":"si","orden":"1","promoI":"0","promoF":"0","islas":"3"},
    ]
  }
  ,{"type":"table","name":"subcategorias_porsi","database":"qqx486","data":
    [
      {"id":"1","id_categoria":"1","nombre":"Restaurantes","color":"","activo":"si","orden":"1","promoI":"0","promoF":"0"},
    ]
  }
  ,{"type":"table","name":"subcategorias_textos","database":"qqx486","data":
    [
      {"id_subcategoria":"1","id_idioma":"1","nombre":"Restaurantes","clave":"restaurantes, pizzer\u00edas, los productos de alimentaci\u00f3n, almuerzos, cenas, fiestas, c\u00f3cteles, cocktails, comen, comer, beber, las cocinsa mediterr\u00e1neas, barras, comedores, comiento, sitios para comer, restaurantes especializados en bistecs, el alimento japon\u00e9s, comida japonesa, el alimento espa\u00f1ol, comida espa\u00f1ola, el alimento italiano, comida italiana, parrillas, la cocina francesa, el alimento alem\u00e1n, comida alemana, comidas, licores, mariscos, el alimento asi\u00e1tico, comida asi\u00e1tica, pescados, mariscos, camarones, gambas, tabernas,"},
    ]
  }
  ,{"type":"table","name":"version","database":"qqx486","data":
    [
      {"id":"32"}
    ]
  }
]

Answer 1

Looks like Ad & Subcategory has a many to many relationship. So rel_anuncio is the intermediate table to make the relationship.

As the rows of the json are in different a order than the way you need to create the records.

Instead try to first create the Ads, then the Categories and the Subcategories at the end.

json_ads = parsed_json.find { |table| table['name'] == 'anuncios' }
ads = {}

json_ads['data'].each do |ad|
  ads[ad['id']] = Ad.create(
    [... ad fields]
  )
end

And the same for Categories:

json_categories = parsed_json.find { |table| table['name'] == 'categorias' }
categories = {}

json_categories['data'].each do |category|
  categories[category['id']] = Category.create(
    [... category fields]
  )
end

Now that you create the Subcategories you have the Hash with Categories to reference them:

json_subcategories = json.find { |table| table['name'] == 'rel_anuncio' }
subcategories = {}

json_subcategories['data'].each do |subcategory|
  subcategories[subcategory['id']] = Subcategory.create(
    category: categories[subcategory['id_categoria'],
    [... ad fields]
  )
end

And the rel_anuncio information will depend on how you implement the relationship.

Hope it helps.