Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bugfix/column names from subquery #1714

Merged

Conversation

mringler
Copy link
Contributor

@mringler mringler commented Mar 12, 2021

Trying to get ModelCriteria::addSelectQuery(): working.

Found several issues:

  • primary table is not set, leading to queries without FROM (addressed here)
  • columns from ModelCriteria::select() are not resolved` (addressed here)
  • subquery column name replacement in outer query does not work
  • subquery columns are not quoted in outer query

This addresses subquery column name replacement in outer query.

Problem example:

$numberOfBooksQuery = BookQuery::create()
->addAsColumn('NumberOfBooks', 'COUNT(Book.Id)')
->select(['NumberOfBooks', 'AuthorId'])
->groupBy('Book.AuthorId');

AuthorQuery::create()
->addSelectQuery($numberOfBooksQuery, 'numberOfBooks', false)
->where('Author.Id = numberOfBooks.AuthorId')     // note that 'AuthorId' is an alias from the subquery
->withColumn('numberOfBooks.NumberOfBooks', 'NumberOfBooks');

leads to query

SELECT ... 
FROM author, (
  SELECT
    COUNT(book.id) AS NumberOfBooks,
    book.author_id AS "AuthorId" 
  FROM book
  GROUP BY book.author_id
) AS numberOfBooks 
WHERE author.id = numberOfBooks.author_id;     -- column author_id does not exist on subquery 

The error is quite simple: When replacing the column literal from a subquery, the column map was also set, which causes the column to be treated as a column belonging to the outer table. Not setting the column map resolves the issue.

Note that the whole column name replacement is very smelly. Look at what happens with the currentAlias class variable if you want to get a whiff. I initially cleaned this up, but realistically, a large commit like this is not going to get reviewed properly, so boiling it down seems like the best option. During refactoring, I found that tests for name replacement were scattered across three files. That cleanup I kept, because it is probably helpful for others. It is in its own commit, should you want to review it separately.

Oh, I have cleaned up two other minor, yet pretty stunning semantic mistakes that I found:

  • two properties were defined in the child class, but accessed in the parent class via $this, when $this generally does not have them. Pretty sad that Pstan was just silenced, when it correctly complained about it.
  • The constructor of BaseModelCriteria did not call the parent constructor, and instead copied its code.

I'll highlight them in the review, so it does not confuse anyone.
Btw. I think theese mistakes highlight a larger issue with the Criteria classes, where it is not clear from their name what they do (what`s a "Critera", particularly in contrast to a "Criterion", and why does it create sql code?), and where extension hierarchy was abused to decompose the classes, leading to arbitrary logical splits between them. This has apparently and understandably left people confused what goes where, including me.

Copy link
Contributor Author

@mringler mringler left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I just realized I never pressed the submit button on my short walkthrough review

@dereuromark
Copy link
Contributor

Shall we move forward? How can we resolve the open comments?

@mringler
Copy link
Contributor Author

Shall we move forward? How can we resolve the open comments?

Marked my walkthrough comments as resolved.

@dereuromark dereuromark merged commit dd4886f into propelorm:master Apr 22, 2021
@mringler mringler deleted the bugfix/column_names_from_subquery-light branch April 22, 2021 11:07
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

3 participants