Yii Framework Forum: Best complex queries design - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Best complex queries design Greatest way to deal with queries on many tables

#1 User is offline   josdel91 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 03-January 16
  • Location:Cameroon

Posted 13 January 2017 - 03:45 AM

Hello,

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!
0

#2 User is offline   djiwie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 12-December 16
  • Location:Netherlands

Posted 25 January 2017 - 05:43 AM

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.
0

#3 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,184
  • Joined: 17-January 09
  • Location:Russia

Posted 25 January 2017 - 07:49 AM

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.
Yii 2.0 Development Cookbook

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#4 User is offline   josdel91 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 03-January 16
  • Location:Cameroon

Posted 26 January 2017 - 10:40 AM

View Postdjiwie, on 25 January 2017 - 05:43 AM, said:

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.


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!
0

#5 User is offline   josdel91 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 03-January 16
  • Location:Cameroon

Posted 26 January 2017 - 10:45 AM

View Postsamdark, on 25 January 2017 - 07:49 AM, said:

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.


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 :) !
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users