arrange() does not work before using collect()

[SulevR]

When trying to use dplyr arrange() function before collect(), it does not work.

Steps to reproduce:

cdm$condition_occurrence %>%
  arrange(condition_concept_id) %>%
  show_query()

ACTUAL RESULT:

  SELECT *
  FROM main.condition_occurrence

EXPECTED RESULT:

  SELECT *
  FROM main.condition_occurrence
  ORDER BY condition_concept_id

[ablack3]

This is because of https://github.com/darwin-eu-dev/omopgenerics/blob/4c614e66893828c2760b5d8a9630f0b2c08f740c/R/arrange.R#L23

@catalamarti, @edward-burn any ideas?

[ablack3]

I think this is just a limitation we have to deal with when writing cross platform dplyr code that needs to work on local tables as well. I'm not sure how to fix it but if you have an idea feel free to open a PR on https://github.com/darwin-eu/omopgenerics.

[SulevR]

This is because of https://github.com/darwin-eu-dev/omopgenerics/blob/4c614e66893828c2760b5d8a9630f0b2c08f740c/R/arrange.R#L23

@catalamarti, @edward-burn any ideas?

The link does not work (gives 404 error)

[ablack3]

The link is to our private code repository unfortunately.

What's your end goal @SulevR? Just to get whole condition table sorted by concept Id?

[SulevR]

Usually I need to sort by date, but sometimes also by multiple columns (person_id, concept, date). For instance, to measure time between visits (to apply some rolling functions etc). Or, when you create some results table and want to sort by the score etc.

[ablack3]

The reason this is happening is that omopgenerics overwrites arrange with window_order when using remote tbl_sql tables.

In general I would not assume that collect preserves row order and would instead sort after collect.

However you can reliably use sorting/window ordering to create derived variables inside your dplyr pipelines that are translated to SQL.

Here are some examples:

library(CDMConnector)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
con <- DBI::dbConnect(duckdb::duckdb(), eunomiaDir())
cdm <- cdmFromCon(con, "main", "main")


# sort after collect not before
cdm$visit_occurrence %>%
  collect() %>%
  arrange(visit_start_date)
#> # A tibble: 1,037 × 17
#>    visit_occurrence_id person_id visit_concept_id visit_start_date
#>                  <int>     <int>            <int> <date>          
#>  1              192595      2894             9201 1923-01-14      
#>  2              314540      4722             9201 1924-02-24      
#>  3              255559      3851             9201 1927-10-11      
#>  4              193667      2906             9201 1929-01-24      
#>  5               13043       191             9201 1929-09-19      
#>  6              192594      2894             9201 1932-08-06      
#>  7                3109        42             9201 1933-10-30      
#>  8              316779      4755             9201 1933-11-06      
#>  9              267032      4017             9201 1934-01-25      
#> 10              308630      4631             9201 1934-08-05      
#> # ℹ 1,027 more rows
#> # ℹ 13 more variables: visit_start_datetime <dttm>, visit_end_date <date>,
#> #   visit_end_datetime <dttm>, visit_type_concept_id <int>, provider_id <int>,
#> #   care_site_id <int>, visit_source_value <chr>,
#> #   visit_source_concept_id <int>, admitting_source_concept_id <int>,
#> #   admitting_source_value <chr>, discharge_to_concept_id <int>,
#> #   discharge_to_source_value <chr>, preceding_visit_occurrence_id <int>

# you can see that the arrange function maps to the order by clause in the window function in this example
cdm$visit_occurrence %>%
  arrange(person_id, visit_start_date) %>%
  group_by(person_id) %>%
  mutate(
    days_since_last =
      visit_start_date - lag(visit_start_date)
  ) %>%
  show_query()
#> <SQL>
#> SELECT
#>   visit_occurrence.*,
#>   visit_start_date - LAG(visit_start_date, 1, NULL) OVER (PARTITION BY person_id ORDER BY person_id, visit_start_date) AS days_since_last
#> FROM visit_occurrence


cdm$measurement %>%
  arrange(person_id, measurement_concept_id, measurement_date) %>%
  group_by(person_id, measurement_concept_id) %>%
  mutate(
    prev_value = lag(value_as_number),
    delta = value_as_number - prev_value
  ) %>%
  show_query()
#> <SQL>
#> SELECT q01.*, value_as_number - prev_value AS delta
#> FROM (
#>   SELECT
#>     measurement.*,
#>     LAG(value_as_number, 1, NULL) OVER (PARTITION BY person_id, measurement_concept_id ORDER BY person_id, measurement_concept_id, measurement_date) AS prev_value
#>   FROM measurement
#> ) q01


# Most common visit type by person
cdm$visit_occurrence %>%
  count(person_id, visit_concept_id, name = "score") %>%
  group_by(person_id, visit_concept_id) %>%
  slice_min(n = 1, order_by = "score") %>%
  show_query()
#> <SQL>
#> SELECT person_id, visit_concept_id, score
#> FROM (
#>   SELECT
#>     q01.*,
#>     RANK() OVER (PARTITION BY person_id, visit_concept_id ORDER BY 'score') AS col01
#>   FROM (
#>     SELECT person_id, visit_concept_id, COUNT(*) AS score
#>     FROM visit_occurrence
#>     GROUP BY person_id, visit_concept_id
#>   ) q01
#> ) q01
#> WHERE (col01 <= 1)

cdmDisconnect(cdm)

^{Created on 2026-01-03 with reprex v2.1.1}

Here is some of chatGPT's answer that also supports this.

So in summary I think you should make sure you're sorting after collect but you should be able to reliably set window ordering for derived varibales in dplyr pipelines using the current versions of dplyr and CDMConnector.

[SulevR]

Sorry to revisit this, but I ran into the same confusion again - particularly, during the data exploration phase. This time, I’m not using collect(), since I’m only manipulating the data for exploratory purposes. For example, to see which patients have the highest number of diseases, I use the following command:

cdm$condition_occurrence %>%
  group_by(person_id) %>%
  summarize(
    num_conditions=n()
  ) %>%
  arrange(num_conditions) %>%
  show_query()

The result, however, is not sorted and I'm getting the wrong (unordered) result:

<SQL>
SELECT person_id, COUNT(*) AS num_conditions
FROM condition_occurrence
GROUP BY person_id

I find this is still confusing, particularly of getting the wrong result without any notification. It is easy to overlook this error and make wrong inference from the result.

My suggestion: if the arrange() does not work this way, I would expect to get a proper warning, at least.

[ablack3]

Yea this is tricky because sometimes we want to arrange to mean window order (like when it's followed by mutate) and other times we want arrange to mean order by (when it's at the end of a pipeline as in your case).

This overwriting of arrange was done in omopgenerics by @edward-burn and @catalamarti and is not in CDMConnector.

Ed and Marti do you guys have any ideas or suggestions for this issue? I really don't think we want to throw a warning everytime arrange is called because these warnings will be everywhere and cause issues with CRAN submissions.

One idea is that when user call arrange with a CDM table we could add both arrange and window_order which should fix this. What do you all think?

arrange.cdm_table <- function(.data, ...) {
  cl <- class(.data)
  .data <- keepClass(.data)
  if ("tbl_lazy" %in% cl) {
    res <- dbplyr::window_order(.data, ...)
  } else {
    res <- dplyr::arrange(.data, ...)
  }
  res <- restoreClass(res, cl)
  res <- restoreAttributes(res, keepAttributes(.data, cl))
  return(res)
}

Could be changed to

arrange.cdm_table <- function(.data, ...) {
  cl <- class(.data)
  .data <- keepClass(.data)
  if ("tbl_lazy" %in% cl) {
    res <- dbplyr::window_order(.data, ...) |> dplyr::arrange(...)
  } else {
    res <- dplyr::arrange(.data, ...)
  }
  res <- restoreClass(res, cl)
  res <- restoreAttributes(res, keepAttributes(.data, cl))
  return(res)
}

[catalamarti]

well the idea was that at some point that method could live in CDMConnector and then CDMConnector could apply it depending on the DBMS (there are dbms that support both arrange and window order, but some others only support window order) thats why as first approach arrange is translated as window order thus working for all DBMS. If collected then arrange works as expected. For the moment this behaviour it is working quite well, but open to discuss.

[catalamarti]

I guess this approach is better for inside the packages where we really need a consistency between DBMS. But maybe outs packages it can be annoying, happy to find a way that this only works inside packages.

[ablack3]

thanks @catalamarti. I agree the current set up is working well. In general we cannot guarantee row order across all supported dbms. For cross platform dplyr code it is best to first collect and then order.

For your use case @SulevR it may be best to opt out of this behavior by defining a helper that removes the cdm_table and omop_table classes. Then everything should work the same as when using dplyr without CDMConnector and omopgenerics.

library(CDMConnector)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
con <- DBI::dbConnect(duckdb::duckdb(), eunomiaDir())
cdm <- cdmFromCon(con, "main", "main")



removeCdmTableClass <- function(tbl) {
  class(tbl) <- class(tbl)[!class(tbl) %in% c("omop_table", "cdm_table")]
  tbl
}

cdm$condition_occurrence %>%
  group_by(person_id) %>%
  summarize(
    num_conditions=n()
  ) %>%
  removeCdmTableClass() %>% 
  arrange(num_conditions) %>%
  show_query()
#> <SQL>
#> SELECT person_id, COUNT(*) AS num_conditions
#> FROM condition_occurrence
#> GROUP BY person_id
#> ORDER BY num_conditions


cdmDisconnect(cdm)

^{Created on 2026-01-19 with reprex v2.1.1}