nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

One-to-first join — Control what related record gets used

Bud

It would be helpful if we could control the logic used for getting the records on the many side of [one-to-first] joins.

For example, choose an ORDER BY field: Grab the related record with the latest date, or the largest integer, etc. Specify ascending or descending order.

Even better, make it a user-defined ORDER BY sql clause, so that we can use specialized logic like:

SQL CASE statement
Multiple sorting fields, some ASC and some DESC
NULLS FIRST or NULLS LAST (relatively unknown SQL functionality)
- Specify how nulls are sorted in the attribute table (nulls first or last)

Find more posts tagged with

Geoprocessing

Status: Open

Comments

Bud

An idea that's similar, but it's for spatial joins, not tabular joins: Spatial Join — "One-to-one (ranked by field)" join operation
Idea: Consistent one-to-one/one-to-first wording in GP tools

Bud

Possible workarounds:

Since one-to-first joins use the related record that has the lowest OBJECTID, we could consider sorting the rows for each grouping/common ID, along with generating a new OBJECTID on the sorted data, using techniques like:

Query layer (enterprise geodatabases only) (untested)

select
    cast(rownum as int) as objectid, --Fake OBJECTID. Uses sorted subquery.
    a1,
    b1
from
    (select
        a1,
        b1
    from
        table_1
    order by
        a1,
        b1 desc)

Database view (mobile or enterprise geodatabase; file geodatabase SQL is likely too limited) (untested)
Sort geoprocessing tool (static output)
Other? Python?

ROW_NUMBER function in FGDB SQL
https://community.esri.com/t5/arcgis-pro-ideas/row-number-function-in-fgdb-sql/idc-p/1526715/highlight/true#M31739

Bud

1:M Join to Pull feature with Specific Attribute information

Sort within groupings, join, and fill gaps using a single tool

Bud

I'm starting to wonder if my original subquery approach was flawed:

Oracle (FETCH):

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      FETCH FIRST ROW ONLY
            )

Reason:
In the join, Pro uses the subquery to pare down the entire query, not just pare down the join table. More info here: Add Join — Create independent definition query on join table (pre-filter)

Bud

@JonathanNeal

This is what I came up with. It's a FGDB database view.

Select the greatest 1 per group, with ties:

select
    *
from
    species_records
inner join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on species_records.t_species = l.t_species 
       and species_records.t_date = l.t_date

Or, select 1 per group, without ties:

select
    *
from
    species_records s
inner join
    (
    select
        min(objectid) as objectid,
        min(t_unique_id) as t_unique_id,
        t_species,
        max(t_date) as t_date
    from
        (select
            *
        from
            species_records
        inner join
            (select
                t_species,
                max(t_date) as t_date 
            from
                species_records 
            group by
                t_species) l
            on species_records.t_species = l.t_species 
               and species_records.t_date = l.t_date
        )
    group by
        t_species
    ) l
    on s.t_unique_id = l.t_unique_id

Source: Selecting the most recent records based on unique values in another field

I couldn't find a way to do it in a SQL expression. Only in a database view.

Sample Excel data: https://community.esri.com/t5/arcgis-pro-ideas/fgdb-database-views-view-that-selects-from-an/idi-p/1369894?attachment-id=78021

JonathanNeal

@Bud (Untested) I didn't get to trying it yet but put comments on what I am hoping will happen.
SQL for reporting and analysis on file geodatabases—ArcGIS Pro | Documentation

--My FGDB table is called: species_records.
--The FGDB view below will be called: latest_vw.

select
    t_species as v_species,
    SUBSTRING(max(CONCAT(EXTRACT(YEAR FROM t_date), EXTRACT(MONTH FROM t_date), EXTRACT(DAY FROM t_date), OBJECTID)), 10, 10) as LastUpdatedOID 
    -- Cast date as text in the format YYYYMMDD
    -- Get Field of interest, OBJECTID in our case
    -- Skip the first 10 characters
from
    species_records
group by
    t_species

Bud

@JonathanNeal

There is a classic FGDB use case in this post: Selecting the most recent records based on unique values in another field.

I tried using your "group by in a database view" technique:

--My FGDB table is called: species_records.
--The FGDB view below will be called: latest_vw.

select
    t_species as v_species,
    max(t_date) as v_date
from
    species_records
group by
    t_species

The requirement is: Select the latest record for each species group. Include all fields from the original table.

When it comes to your group by/view technique, I don't know how to bring all the other fields into the query. In other words, I don't know how to link the original table to the group by view.

I've tried a number of different SQL techniques (see Joins based on multiple fields) and also a "view on a view" that work in a mobile geodatabase but don't work in a file geodatabase, due to various FGDB SQL bugs.

Any ideas?

JonathanNeal

@Bud I group by the join Id and used an aggregate to choose my values of interest.

Select Name, max(Desc) as myD from JoinTable group by Name

Bud

@JonathanNeal I couldn't find a way to "control what related record is used" (such as get the latest inspection for each road) when using a FGDB database view, since FGDB SQL doesn't support correlated subqueries.

JonathanNeal

FYI, another option that is supported on file geodatabases are views:
https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/create-database-view.htm

Bud

@BrantCarman Does this help you at all?

Use a SQL expression inside a Python or Arcade field calculation?
Interestingly, SQL is an option when using the field calculator on a hosted feature service.

I don't use hosted feature services (is that the same thing as AGOL?), so I don't know if it applies or not.

For example, could you do a SQL field calculation in ArcGIS Online, and use some SQL logic like I mentioned above to "grab the most recent record from the join table using ObjectID descending order"?

BrantCarman

Thanks @JonathanNeal I'll make a note to try this out, but unfortunatley entering the "busy season" and not much time to R&D on this project.

My hope is that this idea gets some more traction and ESRI implements sooner rather than later, because it's a great idea and would be an awesome functionality in ArcGIS Pro!

Bud

@BrantCarman

I didn't even think to try your definition query. Any experience trying it with AGOL hosted Feature Layers? Or with setting the definition in a python script?

Sorry, no experience with either.

Edit: What kind of database is AGOL behind the scenes? PostgreSQL? If so, then the SQLite definition query I mentioned might work, since PostgreSQL seems to support the LIMIT clause, just like SQLite.

JonathanNeal

@BrantCarman been working on this in 3.2 the definition queries transfering and updating is mostly correct/better than ArcMap. Maybe my testing script below can be helpful to you in trying this approach.

lyr = arcpy.management.MakeFeatureLayer('country_geom', "lyr_with_def_query")[0]
lyr.updateDefinitionQueries([{'name': 'fc_query', 'sql': "country_geom.ISO_3DIGIT LIKE 'A%'", 'isActive': True}])
tab = arcpy.management.MakeTableView('country_tab', "tab_with_def_query", "ISO_3DIGIT LIKE '%B%'")[0]
tab.updateDefinitionQueries([{'name': 'tab_query', 'sql': "country_geom.ISO_3DIGIT LIKE '%B%'", 'isActive': True}]) #put your own subquery code here
result_with_and_2_def_query = arcpy.management.AddJoin(lyr, 'ISO_3DIGIT', tab, 'ISO_3DIGIT')[0]
dfqs = result_with_and_2_def_query.listDefinitionQueries()
print(dfqs)
result_less_join = arcpy.management.RemoveJoin(result_with_and_2_def_query)[0]
dfqs_remove = result_less_join.listDefinitionQueries() #Check to ensure that it is removed properly
print(dfqs_remove)

One TODO (Not fixed yet):
SubQueries still require manual intervention: https://community.esri.com/t5/arcgis-pro-ideas/join-to-table-with-definition-query-column-name/idi-p/1240170

BrantCarman

@Bud good question. I'm working with Hosted Feature Layers primarily and plugging the layers into a Python script to automate some field calculations between join and input table. I didn't even think to try your definition query. Any experience trying it with AGOL hosted Feature Layers? Or with setting the definition in a python script?

Bud

@BrantCarman Interesting. Yes, it's weird that you can do it in ArcGIS Online, but not ArcGIS Pro (at least not out of the box).

Out of curiosity, is there a reason why the definition query workaround I mentioned above wouldn't work for you?

SQLite/mobile geodatabase (likely works for other databases too):

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      LIMIT 1
            )

I thought that was kind of a neat solution. But it doesn't work for file geodatabases, since they don't have full SQL support.

BrantCarman

I just found out that you can do this in ArcGIS Online join tool. I thought I must have been missing something in Arc Pro all this time, but apparently not!

I've been wishing we had this ability for years. Can't believe it made it to AGOL before Pro!

My work around now is to create a hosted live Join View layer from join tool in map viewer classic, grabbing the most recent record from the join table using ObjectID descending order. This gives me a live layer that I can symbolize from an attribute in my join table (from most recently created record).

I still have a need to get this attribute into my parent table, so now in Arc Pro I join the new live join layer from AGOL back to my parent layer/table and use field calculator to populate that most recent value back into my parent table.

Crazy round-about solution.

BenCunningham

I would love to see this as well, specifically the one-to-first for the latest date, as you said.

Bud

Here's a workaround for enterprise geodatabases and mobile geodatabases (not file geodatabases). It also works for Excel files since ArcGIS uses SQLite SQL for Excel file SQL expressions.

Create a definition query on the join table -- to pare down the rows so that there's only one row for each ID. Examples:

Oracle (FETCH):

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      FETCH FIRST ROW ONLY
            )

SQLite/mobile geodatabase and Excel files (LIMIT):

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      LIMIT 1
            )

(likely works for other databases too)

Those queries only return one row per ASSET_ID (the field I'll be joining on). If there is a tie, then it uses a different field, CONDITION, to break the tie.

Additional tie-breaker fields can be easily added to the ORDER BY clause.
For other use cases, LIMIT 1 (or FETCH) could be changed so that it returns multiple rows per ASSET_ID, not just one row, if required.
More info here: Using a WHERE clause subquery, select the greatest n per group with a tie-breaker

Next, join to the table that has the definition query.

Result:

That lets us control what related record is used in the join. The join is now 1:1 instead of one-to-first or 1:M.

So that satisfies the original problem, but only for datatypes that have full SQL support. So it doesn't help us for file geodatabases or other datatypes that don't support correlated subqueries in SQL expressions. More info: Support correlated subqueries in file geodatabase SQL expressions.

Edit:

On closer inspection, there's a problem with this approach (ArcGIS Pro 3.0.3; mobile geodatabase).

When I try to create the join on the table with the definition query, I get an error:

Failed to refresh table. 
Error: 
Underlying DBMS error [ambiguous column name:
objectid] [Roads][STATE_ID = 0]

I tried adding prefixes to all fields in the subquery, but that had no effect on the error:

objectid IN (
     SELECT r2.objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY r2.date_ DESC, r2.condition DESC
      LIMIT 1
            )

I'm not sure what the cause is.

I have the same problem in an Oracle 18c 10.7.1 enterprise gdb (ArcGIS Pro 2.6.8):

Failed to refresh table.
Error:
Underlying DBMS error [ORA-00918: column
ambiguously defined] [DBJoin1]

Solved:

I had to add a table prefix (aka "fully qualify the table name") for the objectid in the first line:

Before:

 objectid IN (

After:

 roadinsptable.objectid IN (

I've updated the original queries.

Idea: Join to table with definition query — column name should get prefixed (SQL expression with subquery)

BUG-000155819: Attribute table of joined layer in Mobile Geodatabase does not open when a definition query with a subquery is applied to the join table.

Bud

User-defined SQL join clause

GP tool to select greatest n per group

File Geodatabase SQL expression to get greatest n per group [ERROR 000358: Invalid expression]

Select greatest n per group using EXISTS

What SQL standard to use when writing geodatabase-agnostic SQL expressions?

JonathanNeal

Very glad to see this idea come up here!
Currently in 3.1 we have added this ability for the Join Field tool. The same matching code is used for Add Join.

We are considering/tracking this with a support issue for Add join to allow one to first joining.

We can easily implement a one-to-first joining that will use ObjectId to do the joining and require that both data be within the same workspace (on our near term radar).

If there are enough upvotes and comments we may be able to get development time for adding the custom control to expose OrderBy clause as a control.

ScottFedak2085

I feel like I've ran into other use cases via geoprocessing tools, python scripts, etc. where a functionality along these lines could be very useful. It's sometimes difficult to find documentation or troubleshoot what ESRI is actually utilizing to iterate over a collection of objects. You would think it'd be OBJECTID, but I feel like I've ran into situations where that didn't appear to be the case. Even if it were OBJECTID, that doesn't provide a ton of user-ability to control the iteration process.

I like the idea of a user defined clause, and I think that would be preferred as it could be integrated into automation. However, even a general radio button that would specify that a join, geoprocess, ArcPy function, etc. honors how a table or feature class is currently ordered would be a useful upgrade.

JeffWard

I can get behind this.