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

How to create where - group by queries without querybuilder? #501

Closed
milsorm opened this issue Mar 10, 2021 · 7 comments
Closed

How to create where - group by queries without querybuilder? #501

milsorm opened this issue Mar 10, 2021 · 7 comments
Labels

Comments

@milsorm
Copy link

milsorm commented Mar 10, 2021

If I use CountAggregateFunction inside findBy(), every condition from findBy went to HAVING clause. But I want to use all of these in WHERE clause, than perform aggregation and (possibly) something makes in HAVING or leave HAVING empty.

How can I distinguish, which conditions go to WHERE and which to HAVING?

@hrach
Copy link
Member

hrach commented Mar 10, 2021

If I understand your query correctly, you are doing multiple filtering in findBy() and one of them is an aggregation and others not. Yes, it is currently buggy.

Could you please try out this PR which could fix it? It does extensive rewrite of the query so I need to more verification it doesn't break anything else.

@milsorm
Copy link
Author

milsorm commented Mar 11, 2021

No no, I am probably doing something which is outside of ORM scope.

I have a table with many records and repository-mapper-entity for individual access to these records. But I also need classic

SELECT something,COUNT(*) FROM table GROUP BY something

but even if I use queryBuilder for building that query with usage of entity definition, I have no access to COUNT(*) column because it cannot be described in entity. Probably it is necessary to build view for every group-by select and build individual repository-mapper-entity set for every view or use only DBAL layer (no ORM)... Am I right? Or is there any theoretical construct how to create sub-summary over entities without iterating over all records?

@hrach
Copy link
Member

hrach commented Mar 11, 2021

You may override the DbalMapper's builder method to select these data for your entity. But ORM is definitely (edit: NOT) designed for this type of selects. You can also utilize an actual column and update its content with triggers.

@hrach
Copy link
Member

hrach commented Mar 20, 2021

Closing as I don't see potential to support selecting a grouped result explicitly.
You may have try overriding DbalMapper::builder() method to return such query and avoid storing that column.

@hrach hrach closed this as completed Mar 20, 2021
@milsorm
Copy link
Author

milsorm commented Mar 20, 2021

My solution:

  • descend DbalCollection - findByGroupBy which is able to call processFilterFunction through helper for where, group by and having clauses
  • descend Mapper to create my new descendant collection instead of DbalCollection (in findAll()) and allow to call this method from collection
  • descend Repository to allow call this method from mapper
  • create second entity with read properties for aggregation columns
  • add detection to Repository to choose original entity class and aggregation entity class if aggregation fields are returned (which entity to populate)

The only pity is that I need to identify such aggregation column to same name as property in aggregation entity. So I used "aggregatedValue" and it is constant name in my DbalCollection descendant and in aggregation entities.

Much better solution can be some virtual property in entity for storing aggregation values and detect its name in my DbalCollection descendant.

The result is that all rows in aggregated queries are still entities and works perfectly in grids, tables etc.

You wrote "But ORM is definitely designed for this type of selects.". Hmm, there is a way how to use it but a lot of programming - not so stright forward.

@hrach
Copy link
Member

hrach commented Mar 20, 2021

You wrote "But ORM is definitely designed for this type of selects.". Hmm, there is a way how to use it but a lot of programming - not so stright forward.

Oh, sorry, the sentence misses the most important word - NOT. :D

@milsorm
Copy link
Author

milsorm commented Mar 20, 2021

That makes sense :-)

OK, nevermind, solution found, it is only 10 minutes more work in every aggregation.

Thanks for great ORM anyway.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants