⚠ 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

Editing problem - only allow edit of records assigned to user



Kobus

Kobus
  • profile picture
  • Member

Posted 03 February 2013 - 17:20 PM

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

 


davidoster

davidoster
  • profile picture
  • Member

Posted 05 February 2013 - 09:29 AM

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;
	}

Kobus

Kobus
  • profile picture
  • Member

Posted 05 February 2013 - 09:48 AM

Hi David,

 

Thank you for your answer. How would I then implement this? Could I perhaps see the source of your implementation bit? Thanks so much!

 

Kobus


davidoster

davidoster
  • profile picture
  • Member

Posted 05 February 2013 - 10:00 AM

I PM you! ;-D


Kobus

Kobus
  • profile picture
  • Member

Posted 05 February 2013 - 14:20 PM

Thanks, David,

 

Looking at your messages now. :-)

 

Kobus