<commit_before>require Rails.root.join('lib/tasks/helpers_for_import.rb')
namespace :import do

  desc "Import hybrids records from csv files (usage: rake import:hybrids[path/to/file,path/to/another])"
  task :hybrids, 10.times.map { |i| "file_#{i}".to_sym } => [:environment] do |t, args|
    TMP_TABLE = 'hybrids_import'
    taxonomy_id = Taxonomy.where(:name => 'CITES_EU').first.id
    taxon_relationship_id = TaxonRelationship.joins(:taxon_relationship_type).where(:taxon_relationship_types => {:name => 'HAS_HYBRID'}).first.id

    puts "There are #{TaxonConcept.where(:name_status => "H",
      :taxonomy_id => taxonomy_id).count} in the database"

      files = files_from_args(t, args)
      files.each do |file|
        drop_table(TMP_TABLE)
        create_table_from_csv_headers(file, TMP_TABLE)
        copy_data(file, TMP_TABLE)


        # Importing Hybrids, step by step:
        # TaxonConcepts  many to one relationship with taxon_names [scientific_name]
        # 1- Insert all scientific_names into taxon_names table (DISTINCT)
        # 2- Join back to insert the taxon_concepts
        # 3- Create taxon_relationships
        sql = <<-SQL

        INSERT INTO taxon_names(scientific_name, created_at, updated_at)
        SELECT DISTINCT full_hybrid_name,
        now()::date AS created_at,
        now()::date AS updated_at
        FROM hybrids_import;

        INSERT INTO taxon_concepts
        (full_name, 
        ranks_id, 
        taxon_names_id, 
        legacy_trade_code, 
        created_at, 
        updated_at)
        SELECT
        full_hybrid_name, 
        r.id as ranks_id, 
        tn.id as taxon_names_id, 
        legacy_cites_taxon_code as legacy_trade_code,
        #{taxonomy_id},
        now()::date AS created_at,
        now()::date AS updated_at
        FROM hybrids_import
        INNER JOIN ranks r ON hybrid_rank = r.name
        INNER JOIN taxon_names tn ON full_hybrid_name = tn.scientific_name;
        


        INSERT INTO taxon_relationships
        (taxon_concept_id,
        other_taxon_concept_id,
        taxon_relationship_id)
        SELECT 
        species_plus_id,
        id,
        #{taxon_relationship_id}
        FROM hybrids_import
        LEFT JOIN taxon_concepts 
        ON full_hybrid_name = taxon_concepts.full_name


        )
        SQL
        ActiveRecord::Base.connection.execute(sql)
        puts "There are now #{Quota.count} CITES quotas in the database"
      end
  end
end
<commit_msg>Added inner join to confirm species_plus_id<commit_after>require Rails.root.join('lib/tasks/helpers_for_import.rb')
namespace :import do

  desc "Import hybrids records from csv files (usage: rake import:hybrids[path/to/file,path/to/another])"
  task :hybrids, 10.times.map { |i| "file_#{i}".to_sym } => [:environment] do |t, args|
    TMP_TABLE = 'hybrids_import'
    taxonomy_id = Taxonomy.where(:name => 'CITES_EU').first.id
    taxon_relationship_id = TaxonRelationshipType.where(:name => 'HAS_HYBRID').first.id

    puts "There are #{TaxonConcept.where(:name_status => "H",
      :taxonomy_id => taxonomy_id).count} in the database"

      files = files_from_args(t, args)
      files.each do |file|
        drop_table(TMP_TABLE)
        create_table_from_csv_headers(file, TMP_TABLE)
        copy_data(file, TMP_TABLE)


        # Importing Hybrids, step by step:
        # TaxonConcepts  many to one relationship with taxon_names [scientific_name]
        # 1- Insert all scientific_names into taxon_names table (DISTINCT)
        # 2- Join back to insert the taxon_concepts
        # 3- Create taxon_relationships
        sql = <<-SQL

        INSERT INTO taxon_names(scientific_name, created_at, updated_at)
        SELECT DISTINCT full_hybrid_name,
        now()::date AS created_at,
        now()::date AS updated_at
        FROM hybrids_import;

        INSERT INTO taxon_concepts
        (full_name, 
        ranks_id, 
        taxon_names_id, 
        legacy_trade_code,
        taxonomy_id, 
        created_at, 
        updated_at)
        SELECT
        full_hybrid_name, 
        r.id as ranks_id, 
        tn.id as taxon_names_id, 
        legacy_cites_taxon_code as legacy_trade_code,
        #{taxonomy_id},
        now()::date AS created_at,
        now()::date AS updated_at
        FROM hybrids_import
        INNER JOIN ranks r ON hybrid_rank = r.name
        INNER JOIN taxon_names tn ON full_hybrid_name = tn.scientific_name;
        
        INSERT INTO taxon_relationships
        (taxon_concept_id,
        other_taxon_concept_id,
        taxon_relationship_id)
        SELECT 
        taxon_concepts.id,
        other_taxon_concepts.id,
        #{taxon_relationship_id}
        FROM hybrids_import hi
        INNER JOIN taxon_concepts
        ON species_plus_id = taxon_concepts.id;
        LEFT JOIN taxon_concepts other_taxon_concepts
        ON full_hybrid_name = other_taxon_concepts.full_name
        )
        SQL
        ActiveRecord::Base.connection.execute(sql)
        puts "There are now #{Quota.count} CITES quotas in the database"
      end
  end
end

{'fraction_non_alphanumeric': 0.07951854775059194, 'fraction_numerical': 0.001973164956590371, 'mean_word_length': 2.592487597448618, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '790947', 'n_tokens_mistral': 1756, 'n_tokens_neox': 1599, 'n_words': 371}