Editing problem - only allow edit of records assigned to user

Hi all,

Please see the below. I am struggling with what seems to be a MySQL problem rather than a GroceryCRUD issue. I hope someone can see the problem and help me to fix it...

$crud->set_table($this->config->item('db_prefix') . 'company');
$crud->set_subject('Companies');
$crud->required_fields('company', 'category_id', 'active', 'vat_number', 'reg_number', 'telephone', 'address', 'bio', 'picture', 'email');
$crud->set_relation('category_id', $this->config->item('db_prefix') . 'category', 'category');
$crud->set_relation_n_n('Companies', $this->config->item('db_prefix') . 'user_company', $this->config->item('db_prefix') . 'company', 'user_id', 'company_id', '{name} ({reg_number})');

//$crud->where($this->config->item('db_prefix') . 'user_company.company_id', $this->session->userdata('id'));

When I un-comment the "where" clause, I get the following error:

Unknown column 'icms2_user_company.company_id' in 'where clause'
SELECT
`icms2_company`.*, jb583a629.category AS sb583a629, (SELECT
GROUP_CONCAT(DISTINCT CONCAT('', COALESCE(name, ''), ' (',
COALESCE(reg_number, ''), ')')) FROM icms2_company LEFT JOIN
icms2_user_company ON icms2_user_company.company_id = icms2_company.id
WHERE icms2_user_company.user_id = `icms2_company`.id GROUP BY
icms2_user_company.user_id) AS Companies
FROM (`icms2_company`)
LEFT JOIN `icms2_category` as jb583a629 ON `jb583a629`.`id` =
`icms2_company`.`category_id`
WHERE `icms2_user_company`.`company_id` = '26'
LIMIT 50

I hope someone can help me get this right? I do not know what I am doing wrong.

My database structure is like this:

CREATE TABLE IF NOT EXISTS `icms2_company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `active` enum('Yes','No') NOT NULL DEFAULT 'No',
  `reg_number` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE IF NOT EXISTS `icms2_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `active` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

CREATE TABLE IF NOT EXISTS `icms2_user_company` (
  `user_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My end-goal is to select only companies that the user has access to, for managing. So - if companies 13 and 14 is assigned to user 26, then those are the only ones he/she can access via GroceryCRUD.

Thanks in advance!

Kobus

Check a little bit the produced SQL,

the where clause that is generated from your commented line is added at the very end. This means that the where clause is for the very first select (the outside/main one)!!! which doesn't not have of course the column icms2_user_company.company_id.

I would make my own model and my own query, a bit like this... (pasted from a project of mine but it's very similar),

public function get_healing_techniques_id_activities_id_description()
	{
		$query = "SELECT `id`, `activities_id`, `description` FROM `groups` WHERE `activities_id` IN (SELECT `id` FROM `activities` WHERE `description` LIKE 'H-%' ORDER BY `id`) ORDER BY `id` DESC";
		$result = $this->db->query($query);
		return $result;
	}

Editing problem - only allow edit of records assigned to user

Kobus

davidoster

Kobus

davidoster

Kobus