Jump to content


Editing problem - only allow edit of records assigned to user

where mysql set-relation-n-n set-relation

  • Please log in to reply
4 replies to this topic

#1 Kobus

Kobus

    Advanced Member

  • Members
  • PipPipPip
  • 74 posts
  • LocationSouth Africa

Posted 03 February 2013 - 05: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

 


--------------------
Kobus Myburgh
Managing Director: Impero Consulting
http://www.impero.co.za
+27 82 043 8488


#2 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

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

____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#3 Kobus

Kobus

    Advanced Member

  • Members
  • PipPipPip
  • 74 posts
  • LocationSouth Africa

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


--------------------
Kobus Myburgh
Managing Director: Impero Consulting
http://www.impero.co.za
+27 82 043 8488


#4 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 05 February 2013 - 10:00 AM

I PM you! ;-D


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#5 Kobus

Kobus

    Advanced Member

  • Members
  • PipPipPip
  • 74 posts
  • LocationSouth Africa

Posted 05 February 2013 - 02:20 PM

Thanks, David,

 

Looking at your messages now. :-)

 

Kobus


--------------------
Kobus Myburgh
Managing Director: Impero Consulting
http://www.impero.co.za
+27 82 043 8488






Also tagged with one or more of these keywords: where, mysql, set-relation-n-n, set-relation

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users