Filtering does not work on HasMany relations

[DeanMauro]

How to Reproduce

1. Take the two models Component and ComponentVersion, where Component HasMany ComponentVersions:

Component

class Component extends Model {
  public static tableName = 'components';
  public static idColumn = 'component_id';
  public static modelPaths = [ __dirname ];

  // Fields
  public component_id!: number;

  // Relational Fields
  public versions!: ComponentVersion[] | null;
  
  // Relational Mappings
  public static relationMappings() {
    return {
      versions: {
        relation: Model.HasManyRelation,
        modelClass: ComponentVersion,
        join: {
          from: 'components.component_id',
          to: 'component_versions.component_id'
        }
      }
    };
  }
}

ComponentVersion

class ComponentVersion extends Model {
  public static tableName = 'component_versions';
  public static idColumn = 'component_version_id';
  public static modelPaths = [ __dirname ];

  // Fields
  public component_version_id!: number;
  public component_id!: number;
  public component_version_is_active!: boolean;
}

2. Attempt to retrieve all Components and filter on one of ComponentVersion's fields:

  public async getAllComponents(): Promise<Component[]> {
    const request = Component.query().withGraphFetched('versions');
    const filter = { 'versions.component_version_is_active:eq': '1' };

    return await findQuery(Component).build(filter, request);
  }

3. The following SQL is produced:

select `components`.* from `components` 
where exists (
  select 1 from `component_versions` as `ComponentVersion` 
  where `ComponentVersion`.`component_id` = `components`.`component_id` 
  and `component_versions`.`component_version_is_active` like '1'
)

Notice that an alias is given to the relational table's name (in this example, the component_versions table is aliased as ComponentVersion), but this alias is not used on the last line where the query performs the filter operation. Instead, ComponentVersion's table_name is used, causing the query to fail with error:

ER_BAD_FIELD_ERROR: Unknown column 'component_versions.component_version_is_active' in 'where clause'.

When the alias is used, the SQL statement works as intended:

select `components`.* from `components` 
where exists (
  select 1 from `component_versions` as `ComponentVersion` 
  where `ComponentVersion`.`component_id` = `components`.`component_id` 
  and `ComponentVersion`.`component_version_is_active` like '1'
)

Proposed Solution

This issue exists because PropertyRef.buildFilter() aliases XToMany relations, but PropertyRef.fullColumnName() does not. I propose adding this aliasing to PropertyRef.fullColumnName() as well.
Resolved in #81

[vpeltola]

I'm running into this issue as well. Could this fix be released, please?
Here's the bad sql generated by objection-find

'select "store_items".* from "store_items" where exists (
  select 1 from "store_item_products" "Store_Item_Product"
 where "Store_Item_Product"."item_id" = "store_items"."item_id" and "store_item_products"."product_id" in (?)
) and "type" = ?'

The and "store_item_products"."product_id" should be and "Store_Item_Product"."product_id"

[DavidPayne-Woodscamp]

I have also encountered this issue. Here is the bad SQL generated:

select "landowners".* from "landowners" where exists (select 1 from "landowner_parcel" as "LandownerParcel" where "LandownerParcel"."landowner_id" = "landowners"."id" and "landowner_parcel"."parcel_id" = ?)