Cohorts intersection issue using two cohorts in the same table

[annasaura]

cdm$dm2anxiety <- cdm$condition |>
CohortConstructor::requireCohortIntersect(targetCohortTable = "condition",
cohortId = 1,
targetCohortId = 2,
indexDate = "cohort_start_date",
window = c(0, Inf),
intersections = 1,
name = "dm2anxiety")

Error: Failed to prepare query : ERROR: column reference "intersect_cohort" is ambiguous
LINE 2: FROM (SELECT "asl_nomprojectecondition".*, "intersect_cohort...

[edward-burn]

Will need to look more because basic reprex seems to be working

library(omopgenerics)
#> Warning: package 'omopgenerics' was built under R version 4.4.2
#> 
#> Attaching package: 'omopgenerics'
#> The following object is masked from 'package:stats':
#> 
#>     filter
library(CDMConnector)
#> 
#> Attaching package: 'CDMConnector'
#> The following objects are masked from 'package:omopgenerics':
#> 
#>     cdmName, recordCohortAttrition, uniqueTableName
library(CodelistGenerator)
#> Warning: package 'CodelistGenerator' was built under R version 4.4.2
library(CohortConstructor)
#> 
#> Attaching package: 'CohortConstructor'
#> The following objects are masked from 'package:CDMConnector':
#> 
#>     intersectCohorts, unionCohorts

con <- DBI::dbConnect(duckdb::duckdb(), dbdir = eunomia_dir())
cdm <- cdm_from_con(con, cdm_schema = "main", 
                    write_schema = c(prefix = "my_study_", schema = "main"))
#> Support for 'prefix' in write_schema is deprecated and will be removed in a future release. Please use the `writePrefix` argument in `cdmFromCon()` instead.
#> This message is displayed once per session.
cdm
#> 
#> ── # OMOP CDM reference (duckdb) of Synthea synthetic health database ──────────
#> • omop tables: person, observation_period, visit_occurrence, visit_detail,
#> condition_occurrence, drug_exposure, procedure_occurrence, device_exposure,
#> measurement, observation, death, note, note_nlp, specimen, fact_relationship,
#> location, care_site, provider, payer_plan_period, cost, drug_era, dose_era,
#> condition_era, metadata, cdm_source, concept, vocabulary, domain,
#> concept_class, concept_relationship, relationship, concept_synonym,
#> concept_ancestor, source_to_concept_map, drug_strength
#> • cohort tables: -
#> • achilles tables: -
#> • other tables: -

hip_fx_codes <- getCandidateCodes(cdm, "hip fracture")
#> Limiting to domains of interest
#> Getting concepts to include
#> Adding descendants
#> Search completed. Finishing up.
#> ✔ 1 candidate concept identified
#> 
#> Time taken: 0 minutes and 1 seconds
forearm_fx_codes <- getCandidateCodes(cdm, "forearm fracture")
#> Limiting to domains of interest
#> Getting concepts to include
#> Adding descendants
#> Search completed. Finishing up.
#> ✔ 1 candidate concept identified
#> 
#> Time taken: 0 minutes and 1 seconds
fx_codes <- newCodelist(list("hip_fracture" = hip_fx_codes$concept_id,
                             "forearm_fracture"= forearm_fx_codes$concept_id))
cdm$fractures <- cdm |> 
  conceptCohort(conceptSet = fx_codes, 
                exit = "event_start_date", 
                name = "fractures")
#> ℹ Subsetting table condition_occurrence using 2 concepts with domain:
#>   condition.
#> ℹ Combining tables.
#> ℹ Creating cohort attributes.
#> ℹ Applying cohort requirements.
#> ℹ Collapsing records.
#> ✔ Cohort fractures created.

cdm$fractures <- cdm$fractures |> 
  requireCohortIntersect("fractures", 
                         window = c(-Inf, Inf),
                         cohortId = 1, 
                         targetCohortId = 2) 
cdm$fractures
#> # Source:   table<main.my_study_fractures> [?? x 5]
#> # Database: DuckDB v1.0.0 [eburn@Windows 10 x64:R 4.4.0/C:\Users\eburn\AppData\Local\Temp\Rtmp2ZeeH4\file8502a214f27.duckdb]
#>    cohort_definition_id subject_id cohort_start_date cohort_end_date
#>                   <int>      <int> <date>            <date>         
#>  1                    2       1073 2010-08-20        2010-08-20     
#>  2                    2       4283 1956-12-20        1956-12-20     
#>  3                    2        245 1984-02-25        1984-02-25     
#>  4                    2        862 1979-09-11        1979-09-11     
#>  5                    1       3376 2017-10-08        2017-10-08     
#>  6                    2       1320 2006-03-04        2006-03-04     
#>  7                    2       2936 1975-07-24        1975-07-24     
#>  8                    1       5056 2005-10-12        2005-10-12     
#>  9                    2       1145 1977-02-05        1977-02-05     
#> 10                    2       3734 2006-07-11        2006-07-11     
#> # ℹ more rows
#> # ℹ 1 more variable: intersect_cohort <dbl>

^{Created on 2024-12-13 with reprex v2.1.0}

[nmercadeb]

The error might be because the input or intersect table already have an "intersect_cohort" column. Maybe we should revise code to use uniqueId from omopgenerics for internal column names?
Also, to consider using temporary names for internal computations (we do it in some functions but not all) as it would avoid modifying user's input table if the process is interrupted