⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Puzzled with relation 1 to n



buslik

buslik
  • profile picture
  • Member

Posted 15 July 2013 - 11:35 AM

Hi all,

 

I have next DB:

CREATE TABLE IF NOT EXISTS `req` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `desc` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `accTest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reqId` int(11) NOT NULL,
  `desc` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

and I want to have some of acceptance tests rper requirement.

 

right now i have next in my controller:

        $this->grocery_crud->set_table('req');
	$this->grocery_crud->set_relation('id','accTest','{reqId} {desc}');
        $output = $this->grocery_crud->render();

And it only shows requirements, so all linked tests are missing.

 

How can I add acceptance tests to requirement?


buslik

buslik
  • profile picture
  • Member

Posted 15 July 2013 - 14:18 PM

Ok. May be I can do it in other way. Can I add custom button with url on each line of the greed?


davidoster

davidoster
  • profile picture
  • Member

Posted 15 July 2013 - 18:25 PM

Hello [member=buslik] and welcome to the forums.

First of all I would suggest to make a third field on your req table, e.g. 'tests' and make there the set_relation like this

$crud->set_relation('tests', 'accTest', '{reqId} {desc}');

Secondly the primary key 'id' field of a table is handled in a special way, so we avoid to make any relations there because we might end up with strange results.

Thirdly it is good to have a field dedicated per relation othen than the primary key fields since this makes the queries quite faster (if you know how to build the indexes that is!  ;) )

Try the above suggestion and let us know.


buslik

buslik
  • profile picture
  • Member

Posted 16 July 2013 - 10:52 AM

Dear davidoster

 

Thank you for reply.

 

But in my case I need many tests were corresponding to one requirement.

 

So I keep DB as it is, but changed controller;

 $this->grocery_crud->set_table('accTest');
		$this->grocery_crud->set_relation('reqId','req','{id} {desc}'); 
        $output = $this->grocery_crud->render();

it works for view but fails on edit with next message:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, ''), '') as s62b319d1 FROM (`req`) ORDER BY `s62b319d1`' at line 1

SELECT req.id, CONCAT('', COALESCE(id, ''), ' ', COALESCE(desc, ''), '') as s62b319d1 FROM (`req`) ORDER BY `s62b319d1`

Filename: /home/buslik/www/req/models/grocery_crud_model.php

Line Number: 276

to fix it I have changed controller to

$this->grocery_crud->set_table('accTest');
		$this->grocery_crud->set_relation('reqId','req','{req.id} {req.desc}'); 
        $output = $this->grocery_crud->render();

But this broke view sql

Error Number: 1054

Unknown column 'j62b319d1.req.id' in 'field list'

SELECT `accTest`.*, CONCAT('', COALESCE(j62b319d1.req.id, ''), ' ', COALESCE(j62b319d1.req.desc, ''), '') as s62b319d1 FROM (`accTest`) LEFT JOIN `req` as j62b319d1 ON `j62b319d1`.`id` = `accTest`.`reqId` LIMIT 25

Filename: /home/buslik/www/req/models/grocery_crud_model.php

Line Number: 87

my mysql is

| innodb_version          | 1.1.8                   |
| protocol_version        | 10                      |
| version                 | 5.5.28-0ubuntu0.12.04.3 |

buslik

buslik
  • profile picture
  • Member

Posted 16 July 2013 - 11:08 AM

Fixed by next patch in application\models\grocery_crud_model.php

$select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE($related_table.",", ''),'"),str_replace("'","\'",$related_field_title))."') as $field_name_hash";

buslik

buslik
  • profile picture
  • Member

Posted 16 July 2013 - 12:48 PM

Should I submit this fix to git?