idea One-to-first join — Control what related record gets used in ArcGIS Pro Ideas

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

Bud — Wed, 04 Sep 2024 14:38:42 GMT

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)

Re: One-to-first joins: Control what related record is used

JeffWard — Mon, 05 Dec 2022 17:14:25 GMT

I can get behind this.

Re: One-to-first joins: Control what related record is used

ScottFedak2085 — Mon, 05 Dec 2022 18:06:29 GMT

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.

Re: One-to-first joins: Control what related record is used

JonathanNeal — Mon, 05 Dec 2022 18:55:55 GMT

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.

Re: One-to-first joins: Control what related record is used

Bud — Thu, 15 Dec 2022 04:24:25 GMT

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?

Re: One-to-first joins: Control what related record is used

Bud — Sat, 06 Jan 2024 07:24:15 GMT

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.

Re: One-to-first joins: Control what related record is used

BenCunningham — Wed, 22 Mar 2023 13:03:09 GMT

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

Re: One-to-first joins: Control what related record is used

BrantCarman — Mon, 17 Apr 2023 16:08:54 GMT

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.

Re: One-to-first joins: Control what related record is used

Bud — Mon, 17 Apr 2023 17:40:46 GMT

@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.

Re: One-to-first joins: Control what related record is used

BrantCarman — Mon, 17 Apr 2023 17:55:31 GMT

@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?

Re: One-to-first joins: Control what related record is used

JonathanNeal — Mon, 17 Apr 2023 19:12:16 GMT

@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

Re: One-to-first joins: Control what related record is used

Bud — Mon, 17 Apr 2023 21:58:58 GMT

@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.

Re: One-to-first joins: Control what related record is used

BrantCarman — Tue, 18 Apr 2023 17:36:50 GMT

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!

Re: One-to-first joins: Control what related record is used

Bud — Wed, 19 Apr 2023 13:42:38 GMT

@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"?

Re: One-to-first joins: Control what related record is used

JonathanNeal — Fri, 09 Jun 2023 16:38:59 GMT

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

Re: One-to-first joins: Control what related record is used

Bud — Fri, 09 Jun 2023 17:53:41 GMT

@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.

Re: Join: Control what related record is used

JonathanNeal — Fri, 09 Jun 2023 20:14:25 GMT

@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

Re: Join — Control what related record gets used

Bud — Mon, 15 Jan 2024 01:18:13 GMT

@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?

Re: Join — Control what related record gets used

JonathanNeal — Fri, 12 Jan 2024 01:11:52 GMT

@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

Re: Join — Control what related record gets used

Bud — Mon, 15 Jan 2024 06:53:57 GMT

@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