Namespace/prefix generated aliases with `$` or similar about join-monster HOT 5 CLOSED

Yeah, now I see it clearly!

If you want to submit a PR we can include this in the next release.

I would by default have no prefix so we don't introduce any breaking changes and add an option to opt in for a prefix.

from join-monster.

Interesting!

Can you show with a https://github.com/join-monster/join-monster-sscce that this actually happens?

Becase in the method that you can write raw sql, you have a variable with what will be the name of the table. So I'm not sure which kind of query could break that.

from join-monster.

Thanks for the quick reply.

Yes, we can write raw SQL, but we don’t know what alias join-monster will pass to our join/where expression function.

If we use the alias ‘a’ in our expression, it could clash with the ‘a’ that join-monster assigns to its first table reference. This conflict arises because join-monster assigns aliases automatically, and we might unknowingly use the same alias.

Since it’s common to use single letters in SQL subqueries, I’d prefer if join-monster avoided using single-letter aliases. Instead, it would be better if join-monster prefixed its generated aliases with a ‘$’ to prevent any clashes with our own aliases.

See
https://github.com/join-monster/join-monster/blob/master/src/alias-namespace.js

I would do something as trivial as:

  generate(type, name) {
    // if minifiying, make everything ugly and unique.
    if (this.minify) {
      // tables definitely all need unique names
      if (type === 'table') {
-       return this.mininym.next().value.join('')
+       return `$${this.mininym.next().value.join('')}`;
      }

      // but if its a column, we dont need to worry about the uniqueness from other columns
      // because the columns will get prefixed with the parent(s)
      if (!this.columnAssignments[name]) {
        this.columnAssignments[name] = this.mininym.next().value.join('')
      }

      return this.columnAssignments[name]
    }
  }

Edit:

Here is an excerpt from the SQL query join-monster generated with minify: true:

SELECT
  ...
  "az"."id" AS "ap__az__b",
  "az"."name" AS "ap__az__h",
  "az"."color" AS "ap__az__ao"
FROM (
  SELECT "a".*, count(*) OVER () AS "$total"
  FROM property_ext "a" -- <- you can see join-monster has assigned property_ext to "a"
  WHERE ("a".deleted_at IS NULL AND lower((
          SELECT p.first_name || ' ' || p.last_name
          FROM agent AS a -- but I have also used "a" in my WHERE subquery
          INNER JOIN person AS p ON a.person_id = p.id
          WHERE a.id = "a".agent_id -- causing a clash on this subquery WHERE expression
          LIMIT 1
        )) LIKE '%' || lower('a') || '%' AND "a".id NOT IN (
...

from join-monster.

Thank you @nicoabie! PR ready for review.

from join-monster.

FIxed in #533

from join-monster.