Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

Custom query for admin generator

Scene from "Cabaret" by Bob Fosse (1972)

introduction

Do you want to use a specific query for retrieving objects used for running an admin generator? For example, you want to display information about related database records but you see that generating such page generates lots of SQL queries? It's very easy to set a custom query for admin generator in Symfony!


solution

You need to do only 1 simple thing - override the buildQuery method in your actions.class.php (of an admin module).

protected function buildQuery()
{
  return parent::buildQuery()
    ->leftJoin('p.Customer c');
}

parent::buildQuery() is the default query retrieving data for the admin. You may modify it as muh as you want (or you may even build your query from the beginning). The most important use of this feature is decreasing number of SQL queries, which makes an admin panel work a lot faster!

4 comments:

  1. There is another way of doing this. I think it's quite better.

    You can specify custom method for building query. In order to make this work you should modify generator file:

    config:
    list:
    table_method: buildCustomQuery

    Of course the method should retrieve Doctrine_Query object.

    ReplyDelete
  2. Lukasz's method is defined here:

    http://www.symfony-project.org/jobeet/1_4/Doctrine/en/12#chapter_12_sub_table_method

    and it does appear to be the preferred method.

    ReplyDelete
  3. The elaborated answer for less experienced users is:

    1)Create the method you want to use in your Model Table class i.e.
    Model is CompanyStaff
    Model table is CompanyStaffTable
    //We want to retrieve only waiters, so define this function in the table class
    public function retrieveWaiters(){
    return $this->createQuery ()->where ( 'stafftype=?', 'WAITERS' );
    }

    2) Define the list table method to use in you generator.yml file i.e.
    config:
    actions: ~
    fields: ~
    list:
    table_method: retrieveWaiters

    ReplyDelete
  4. Building a custom query is definitely the way to go. Overwriting the main buildQuery on the table will affect the object throughout it's use.

    Another option is to overwrite the buildQuery in the current action. If you have some class constants or methods you need to call, to pass some results into the query, you can.

    ReplyDelete