Best complex queries design

(Tedjoujospin) #1


I’am working on a new project and this question comes another time.

Where should i put sql queries that concerns many active records may be more than 3.

  1. Should i put them in one of the activerecords?

  2. Should i create a class for each type of query (for example one query class for all complex products queries another for customers accounts…). By complex i meant a query that joins many tables!

  3. if it is 2 the best, where should i put those classes? My project is divided into modules and some queries can be used in different modules.

Hope it is too clear and comprehensible if not please let me know.

Thanks in advance!

(Geert) #2

What kind of queries are you executing? Are they reporting-like (e.g., totals, averages, etc.) or is it some kind of advances search query where many objects are filtered?

For the first kind, I would create a separate model which executes all reporting queries. For the second type, I guess it depends, but usually I would add some methods to the main object. Say, you have a table ‘user’ and a table ‘company’, and you wish to query all users from a certain company, I would add a method ‘getCompanyUsers($company)’ to the User model. This method would execute an ActiveQuery, joining the company table and filtering records as needed.

(Alexander Makarov) #3

I’d choose approach #2. These are usually called repositories but you may call these providers or whatever fits best. As for where to put them… anywhere. I’m creating a separate namespace for these such as app\repositories.

(Tedjoujospin) #4

Thanks djewie. But one problem witn putting query functions in ActiveRecords is that if you generate them (using GII) anoother time, you will lose all your functions. Sometimes it happens!

(Tedjoujospin) #5

That’s the best i think too. I have created a directory named “providers” in @app/common/. My class will not inherite from any other yii2 core Class.

Thanks :) !