Aliases with period separator are disregarded

[webmdt]

Version - 2.1.10

Having an issue where the aliases are being removed if using a period separator. For any other separator, the aliases stay intact.

Example:

test.id (test.id)
will output
id

test.id (test_id)
will output
test_id

private const DB_TABLE = 'test';
private const COLUMN_REF = 'test.';

$columns = [
	self::DB_TABLE . '.id (' . self::COLUMN_REF . 'id)',
	self::DB_TABLE . '.join_id (' . self::COLUMN_REF . 'join_id)',
	self::DB_TABLE . '.amount (' . self::COLUMN_REF . 'amount)',
	self::DB_TABLE . '.title (' . self::COLUMN_REF . 'title)',
	self::DB_TABLE . '.status (' . self::COLUMN_REF . 'status)',
	self::DB_TABLE . '.username (' . self::COLUMN_REF . 'username)',
	self::DB_TABLE . '.' . self::CREATED_COLUMN . ' (' . self::COLUMN_REF . self::CREATED_COLUMN . ')',
	self::DB_TABLE . '.' . self::CREATED_BY_COLUMN . ' (' . self::COLUMN_REF . self::CREATED_BY_COLUMN . ')',
	self::DB_TABLE . '.' . self::DELETED_COLUMN . ' (' . self::COLUMN_REF . self::DELETED_COLUMN . ')',
	self::DB_TABLE . '.' . self::DELETED_BY_COLUMN . ' (' . self::COLUMN_REF . self::DELETED_BY_COLUMN . ')',
	self::DB_TABLE . '.' . self::UPDATED_COLUMN . ' (' . self::COLUMN_REF . self::UPDATED_COLUMN . ')',
	self::DB_TABLE . '.' . self::UPDATED_BY_COLUMN . ' (' . self::COLUMN_REF . self::UPDATED_BY_COLUMN . ')',
];

return $this->dbInit->select(
	self::DB_TABLE,
	$columns
	BaseModel::getConditions()
);

Output QUERY:

SELECT test.id,test.join_id,test.amount,test.title,test.status,test.username,test.created,test.createdBy,test.deleted,test.deletedBy,test.updated,test.updatedByFROMtestORDER BYtest.id DESC LIMIT 10 OFFSET 0

Expected Output:

SELECT
`test`.`id` as `test.id`,
`test`.`join_id` as `test.join_id`,
`test`.`amount` as `test.amount`,
`test`.`title` as `test.title`,
`test`.`status` as `test.status`,
`test`.`username` as `test.username`,
`test`.`created` as `test.created`,
`test`.`createdBy` as `test.createdBy`,
`test`.`deleted` as `test.deleted`,
`test`.`deletedBy` as `test.deletedBy`,
`test`.`updated` as `test.updated`,
`test`.`updatedBy` as `test.updatedBy`
FROM `test` ORDER BY `test`.`id` DESC LIMIT 10 OFFSET 0

[catfan]

In SQL, the alias character is accepted A-Z, 0-9, and _, other characters are not recommended.

And there's no need to use the alias for your case. You can refer to the column directly with test.id, or replace the alias name to test_id or testId.

[webmdt]

@catfan, thank you for your input.

The fundamental problem at hand pertains to the construction of query strings within a Filter/Pagination/Search implementation, specifically concerning the utilization of an underscore (_) as a delimiter. This practice conflicts with SQL's query syntax, leading to inconsistencies.

Notably, underscores are commonly employed in table naming conventions across various frameworks and CMSs, such as WordPress, Laravel, and Joomla. This widespread use presents a challenge: employing underscores as alias delimiters in our context is problematic. Implementing a find-and-replace operation based on underscores becomes ineffective due to this naming convention.

Technical Illustration

• Table 1 (Name: test):

  • Columns: id, title, join_id

• Table 2 (Name: test_join):

  • Columns: id, title, description

• Example SQL Query:

  SELECT * FROM test JOIN test_join ON test_join.id = test.join_id

• Resultant Output Issues:

  • Ambiguity in identifiers like test_join_id, which could reference either the id from the test_join table or the join_id from the test table.
  • A query lookup for the string "test_" erroneously includes both test and test_join tables, due to the shared underscore prefix.

Proposed Solution

A potential resolution is to adopt a distinct character, such as a period (.), as the delimiter. This change would allow table and column names to retain the underscore, while aliases would utilize the period, thereby resolving the ambiguity.

@catfan, in light of your remarks on permissible characters for aliases, which also apply to table and column names, a strategic decision may be necessary. This decision would involve either forgoing the use of underscores in table and column names, reserving them exclusively for alias demarcation, or adopting an alternative delimiter strategy to maintain clarity in query string construction.

Any input you have would be greatly appreciated. I can send you the BASE MODEL and the TEST MODEL that this is being utilized in if you'd be willing to review them.

[catfan]

@webmdt Thanks for the sample. I need to test more databases for this case and check out what alias characters can be accepted.

However, to avoid the name conflict, the best practice is to adopt different naming styles for the table and the column, or any way that can return different strings. It's up to the developer because as a database framework, it cannot check the name conflict problem.

Also, if you want to allow the alias to accept the period ., you can replace <alias>[\p{L}_][\p{L}\p{N}@$#\-_] to <alias>[\p{L}_][\p{L}\p{N}@$#\-_\.] (only four places in the project), and test whether it can resolve your problem just now.