PDA

View Full Version : Show and update related tables in a grid



Neopulse
17 Oct 2013, 10:05 AM
Hi I have try exten for a mount or so, so Im and still leaning.
Here my trouble: I have a table related with another two, I means in the table one I store the reference (the ID) of one record of table 2 and one of the table 3, and some other fields.

My question what is the better way to show the table 1 in a grid, replacing the stored values with the related values in the table2 and table3, and of course to edit the the record (I need to edit and add some record only in table 1 but with the id values of tables 2 and 3)

I have a couples of ideas of how to do it, and can use a joint SQL query to get the record and create a form with combobox (store the id and show other field) to save the changes, or I can use the renderer property to change the values, but wish is the best way to do it. There is any available example to use as reference???

Thanks for you time and excuse my english

castitas
17 Oct 2013, 10:22 AM
Can you post a picture or some code?

Neopulse
17 Oct 2013, 11:54 AM
As I said, it more a conceptual advice about the better way (at least a test it one) to show related records in a grid, than a specific issue.
example: I have 3 related tables.


Table visit
field id->autonum int(11)
field id_user ->int(11)
field id_pc ->int(11)
more fields......

Table user
field id ->autonum int(11)
field name ->varchar(50)
more fields....

Table pc
field id ->autonum int(11)
field name ->varchar(20)
more fields...


Those are InnoDB tables with it foreing keys


ALTER TABLE `visit`
ADD CONSTRAINT `visit_ibfk_5` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `visit_ibfk_6` FOREIGN KEY (`id_pc`) REFERENCES `pc` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;


When show the visit table in a grid (or plain mode) I just see


record1 -- id= 1, id_user=1, id_pc=3
record2 -- id= 2, id_user=2, id_pc=5
and more record
/CODE]

I need the values of user.name, that is store in the related table user, instead just the id_user and pc.name instead id_pc.

I know I can create a joint query like
[CODE]
SELECT `visit` . * , `user`.`name` , `pc`.`name`
FROM visit
LEFT JOIN `controlab`.`user ON `visit`.`id_user` = `user`.`id`
LEFT JOIN `controlab`.`pc` ON `visit`.`id_pc` = `pc`.`id`


And pass those record through the proxy method to extens, that's a common task, but then how to edit the records??, can I use a cell or row editing plugin??, or I should populate an editing form??

I'm just looking for suggestions or a related examples, I think is a common issue to show related record in a table or grid, so I suppose I'm not the first with this concern, but I could not find something clear in previous post.

Excuse my English, please

ettavolt
17 Oct 2013, 10:41 PM
Well, Ext natively supports one record per row. If you want to show/edit associations of main record, you will need to specify renderers yourself plus create a form that will correctly show/update such a combination.

castitas
29 Oct 2013, 7:56 AM
You could also look into a tree grid (http://docs.sencha.com/extjs/4.2.2/#!/example/build/KitchenSink/ext-theme-neptune/#tree-grid), though I don't know if it is the best solution to your problem. As for clicking on the row and having it populate a form, you'd need to gather the info on your own. I would create a model representing the three records. Then you can get the three records however you'd like, combine them into an object, and then create the model with the data.