Ask your Symfony questions! Pay money and get answers fast! (more info)

How to control what data is SELECTed in forms with foreign keys? Symfony

  • SOLVED


Symfony 1.4 and Doctrine.

I have a form that is getting too much information because of foreign keys, so that it takes 30 to 60 seconds for the page to load.

This form is for a "wine" table that has foreign keys to 8 other tables, and those other tables hold a lot of data. So my form code looks like this:

   <th><?php echo $form['appellation_id']->renderLabel() ?></th>
       <td>
         <?php echo $form['appellation_id']->renderError() ?>
         <?php echo $form['appellation_id'] ?>


This creates a select box listing the names of all appellations. But Symfony/Doctrine basically does a "SELECT * FROM appellation" instead of a "SELECT name FROM appellation". I need this latter call. At least then I can put an index on name and get a big speed boost. So I might use code like this:

$q = Doctrine_Query::create()
->select('a.name')
->from('Appellation a');


And then I could manually write the code to create the select box.

And then I would remove these foreign key relations from the schema:

relations:
Appellation:
local: appellation_id
foreign: id
Country:
local: country_id
foreign: id
Region:
local: region_id
foreign: id
Color:
local: color_id
foreign: id
TypeOfWine:
local: type_id
foreign: id
SubType:
local: sub_type_id
foreign: id
Container:
local: container_id
foreign: id


However, this means giving up on the purity of Symfony's form system. I would be giving up on all the nice things that Symfony's form system does for me automatically. So I'm wondering, is there a way to keep the foreign relation and have Symfony automatically get the name of the appellations without me having to give up on the form system?

In other words, instead of this:

SELECT w.id AS w__id, w.appellation_id AS w__appellation_id, w.alternate_name AS w__alternate_name, w.alternate_auction_name AS w__alternate_auction_name, w.average_price AS w__average_price, w.bottle_second AS w__bottle_second, w.bottle_second_request AS w__bottle_second_request, w.bottle_code AS w__bottle_code, w.bottle_code_b AS w__bottle_code_b, w.case_type AS w__case_type, w.cases_imported AS w__cases_imported, w.cases_produced AS w__cases_produced, w.case_order AS w__case_order, w.cen AS w__cen, w.cey AS w__cey, w.ccode AS w__ccode, w.created_at AS w__created_at, w.color_id AS w__color_id, w.created_by AS w__created_by, w.container_id AS w__container_id, w.corked AS w__corked, w.country_id AS w__country_id, w.comments AS w__comments, w.current_price AS w__current_price, w.cycle AS w__cycle, w.date_received AS w__date_received, w.date_requested AS w__date_requested, w.dupes AS w__dupes, w.dupe_checker AS w__dupe_checker, w.duplicate AS w__duplicate, w.edited AS w__edited, w.early_tasting_reference AS w__early_tasting_reference, w.extra_type_information AS w__extra_type_information, w.flight AS w__flight, w.flight_b AS w__flight_b, w.flight_bottle AS w__flight_bottle, w.full_name AS w__full_name, w.grape_id AS w__grape_id, w.label AS w__label, w.late_tasting_reference AS w__late_tasting_reference, w.mark AS w__mark, w.name AS w__name, w.notes AS w__notes, w.office AS w__office, w.price AS w__price, w.region_id AS w__region_id, w.region_for_winery AS w__region_for_winery, w.sav_rdate AS w__sav_rdate, w.setup AS w__setup, w.is_setup AS w__is_setup, w.specdes AS w__specdes, w.style AS w__style, w.sub_type_id AS w__sub_type_id, w.tasting_highlights AS w__tasting_highlights, w.tasting_reference_second AS w__tasting_reference_second, w.tasting_reference_third AS w__tasting_reference_third, w.tca AS w__tca, w.tca_final AS w__tca_final, w.type_id AS w__type_id, w.updated_by AS w__updated_by, w.updated_at AS w__updated_at, w.vintage AS w__vintage, w.wnycode AS w__wnycode, w.export_record_from_filemaker_id AS w__export_record_from_filemaker_id, w.copy_edited AS w__copy_edited, w.issue_cycle AS w__issue_cycle, w.do_not_publish AS w__do_not_publish, w.drink_recommendation AS w__drink_recommendation, w.drink_recommendation_two AS w__drink_recommendation_two, w.finished AS w__finished, w.flight_two AS w__flight_two, w.hot_wine AS w__hot_wine, w.importer AS w__importer, w.last_update AS w__last_update, w.late_tasting AS w__late_tasting, w.print_note AS w__print_note, w.rating AS w__rating, w.rating_two AS w__rating_two, w.record_number_current AS w__record_number_current, w.record_id AS w__record_id, w.rt_ok AS w__rt_ok, w.rt_count AS w__rt_count, w.rt_mark AS w__rt_mark, w.retaste_score AS w__retaste_score, w.score AS w__score, w.score_two AS w__score_two, w.tasted_twice AS w__tasted_twice, w.tasting_reference AS w__tasting_reference, w.tcat AS w__tcat, w.web_only AS w__web_only, w.wine_code AS w__wine_code, w.winery_id AS w__winery_id, w.wineryful AS w__wineryful, w.wineryful_two AS w__wineryful_two, w.wyn_code AS w__wyn_code FROM wine w WHERE (w.id = ?) LIMIT 1 - (9)

I just need this:

SELECT name FROM appellation

Answers (1)

2011-03-11

Ivan Rey answers:

You can give the option to the widget to use your own query instead of the default one. You can use the query option. So it would be something like this.



//in the configure method of the form

$query = Doctrine_Query::create()
->select('a.name')
->from('Appellation a');

$this->getWidget('appellation_id')->setOption('query',$query);




That way the widget will use your query. If you need something more advanced you could check out the other options or you could create your own widget class and overload the getChoices() method.


Lawrence Krubner comments:

Interesting. I do this in the form class or in the action code?


Lawrence Krubner comments:

Sorry, I see "$this->getwidget" so you must be talking about the form class.


Lawrence Krubner comments:

Does this understand that it should be in a select? In WineForm configure(), I have:

$this->widgetSchema['appellation_id'] = new sfWidgetFormDoctrineChoice(array('model' => 'Appellation', 'multiple' => false, 'add_empty' => true, 'order_by' => array('name', 'asc')));



Lawrence Krubner comments:

Damn, that is impressive. It correctly guesses that I also want the id and that I want to order by name asc:

SELECT a.id AS a__id, a.name AS a__name FROM appellation a ORDER BY a.name asc

Very clever code.


Ivan Rey comments:

I'm glad you got it to work :)