How to Handle Table Alias Name Clashes

[westonganger]

The API is now implemented how I desired it. However I have now realized that we have a limitation when 2 associations from different models have the same name

For example:

search_scope :search, attributes: [
    :title,
    :content,
    author: [
      :first_name,
      :last_name,
    ],
    comments: [
      :content,
      author: [
        :first_name, 
        :last_name,
      ],
    ],
  ]

In this example we have the author association on the parent model, and the author association on the Comment model.

When the query is created, we usually do LEFT OUTER JOIN table_name AS association_name. So this table alias would have been used twice causing an error.

As stated above, the API is now implemented how I desired it so any further changes are likely to be a slight downgrade to the cleanliness of the API. Regardless this will need to be resolved somehow.

Some possible options to resolve this:

1. Changes table aliases to t1, t2, t3, etc similar to search_cop gem. Then when using SQL strings we would have to reference the table name as a symbol like so :author.first_name and perform the mapping under the hood. I dont like this approach because the developer has no idea what the table aliases are for really custom sql.

2. Add a way to set custom aliases, I would prefer this approach however getting the syntax right will be difficult. See the following possible implementations. I think 2B is the most easy on the eyes, however its a little weird to throw a random options hash at the beginning of an attributes array.

2A:

search_scope :search, attributes: [
    :title,
    :content,
    author: [
      :first_name,
      :last_name,
    ],
    comments: [
      :content,
      "author AS comment_author": [
        :first_name, 
        :last_name,
      ],
    ],
  ]

2B - I think this is my preffered syntax:

search_scope :search, attributes: [
    :title,
    :content,
    author: [
      :first_name,
      "authors.last_name",
    ],
    comments: [
      :content,
      author: [
        {table_alias: "comments_authors"},
        :first_name, 
        "comments_authors.last_name",
      ],
    ],
  ]

2C:

search_scope :search, attributes: [
    :title,
    :content,
    author: {
      attributes: [
        :first_name,
        :last_name,
      ],
    ],
    comments: {
      attributes: [
        :content,
        author: {
          table_alias: "comment_author",
          attributes: [
            :first_name,
            :last_name,
          ],
        },
      ],
    },
  ]

Looking for feedback if any wants to chime in.

[westonganger]

Option 3: Set the table aliases to the full nested name. The above example would use author for the first table alias and comments_author for the second occurence. In this way the table alias is always comprised of all the relationships before it. Example with one more level of relationship could be comments_author_posts.

Note: Apparently MySQL has a limit of 256 (or 255) characters for table alias, we will have to consider this. I tested Postgresql, MariaDB, SQLite and they all seem to NOT have this limitation. Could possibly just add a customized exception for this to guide the developer to manually change the table alias, since the optional options hash is not super obvious.

[westonganger]

Option 4: Use the regular table name until a duplicate conflict occurs, at which point we will automatically alias the conflicting table to the full nested name as described in Option 3. We would likely also include option 2B to allow the user to customize the alias name.

[westonganger]

I think I have found a way that I like without the user having to configure the table aliases

Option 5:

search_scope :search, attributes: [
  :title,
  :content,
  author: [
    :first_name,
    :last_name, 
    some_field: ->(column, comparison_operator, raw_value){ 
       ### ... see below ...
    },
    "json_b_field ->> foo ->> bar" => Proc.new{|column, comparison_operator, raw_value|
      ### ... first, modify any values you want ...

      SearchArchitect.build_like_condition(
        column: "CAST(#{column} AS CHAR)", 
        comparison_operator: comparison_operator,
        raw_value: raw_value, 
        left_wildcard: true, ### defaults to true
        right_wildcard: true, ### defaults to true
      )
      ### OR custom sql
      SearchArchitect.build_custom_condition(
        column: "CAST(#{column} AS CHAR)", 
        comparison_operator: "LIKE",
        raw_value_sql: quote("%#{quote_like_conditions(raw_value)}%"), 
      )
      ### OR
      "CAST(#{column} AS CHAR) #{comparison_operator} #{quote("%#{sanitize_like_conditions(raw_value)}}%'")}"
    },
  ],
]