When to use static query and dynamic query

By darrylmabini on 8 Mar 2011 at 03:44 UTC

Hi all,

Please help me to understand. because I don't know when to use dynamic query and when to use static query.

like for example I want to join three tables. table_one, table_two and table_three.
for static:

  $select = db_query('SELECT field1, field2 FROM {table_one} t1 INNER JOIN {table_two} t2 ON t1.id = t2.id INNER JOIN {table_three} t3 ON t3.tbl_three_id = pcl.tbl_three_id');

for dynamic:

  $select = db_select('table_one', 't1');
  $select->join('table_two', 't2', 't2.id = t1.id');
  $select->join('table_three', 't3', 't3.tbl_three_id = t2.tbl_three_id');
  $select = $select->fields('t1', array('field1', 'field2'));
  $select = $select->execute();

Both queries works fine.

Please help..

Thanks,
Darryl

Comments

hi frnd..., I feel both are

hilarudeens commented 8 March 2011 at 06:47

hi frnd...,
I feel both are same... we have to switch-over between these based on logic that we are implementing....

for example... In the above showed query i want the "field1" in ascending order then we going to static querying type......
likewise if table_three is need only when i logged in as a "admin" then i can write the condition as follows....

...
...
 If($user->uid==0){
  $select->join('table_three', 't3', 't3.tbl_three_id = t2.tbl_three_id');}
...
..

personally i feel, For pagination control static querying model is appropriate and if it is resulting rows is numerous but based on minimal table then dynamic query is right choice...
The things discussed above are thoughts of me only.... Some other may give you another look...
good luck...,

The Drupal documentation says

ayush mittal commented 8 March 2011 at 07:02

The Drupal documentation says that both static and dynamic can be used for select statements.
The multiple joins involved in your case makes the use of Dynamic query more easy.Also if the size of these tables are large and a single query may take more than 10 seconds to execute then You should definitely go for dynamic query.

On a general note the use of static query or dynamic query is a question that requires considering many points.Some of these

1)Nature of SQL statements
(For DDL statements you should go for Dynamic queries)

2)Use of predicates like Range Validations. If these are used very often then go for dynamic queries.

3)Repetitious Execution - if you query has to run just once go for Static else Dynamic

_

WorldFallz commented 8 March 2011 at 19:08

Also see #835068: Document usage of static vs dynamic queries which has some excellent info.

Thanks..

darrylmabini commented 9 March 2011 at 07:00

Hi guys thank you for the reply.

Is this also something to do in the different database type?

Since Drupal 7 can handle MySQL, MariaDB, Postgre or SQLite. Someone told me that the dynamic query is for the compatibility for the database types that drupal 7 can handle.

What is diffrence between staitc query and dynamic query

fawwad.nirvana commented 16 February 2017 at 06:23

After reading all comments still i am unable to understand what is major or minor difference between both.If any body knows then please help me with a proper example. Is there any performance effect?