Jump to content


Problem with alias while setting relation with same table.

search set_relation

  • Please log in to reply
5 replies to this topic

#1 Manu Sharma

Manu Sharma

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationIndia

Posted 05 July 2013 - 07:18 AM

Im getting this error while searching and if i use alias while setting relation then searching works but on add and edit page i get the another error,
 

 

Column 'first_name' in where clause is ambiguous

SELECT * FROM (`users`) LEFT JOIN `users` as j6be37982 ON `j6be37982`.`id` = `users`.`parent_id` WHERE `first_name` LIKE '%vip%' OR `email_id` LIKE '%vip%' OR `user_type` LIKE '%vip%' OR `company_name` LIKE '%vip%'

Filename: C:\wamp\www\event_scheduler\system\database\DB_driver.php

Line Number: 366

 

function index()
    {
     try{
        
            if(!$this->common->check_login() ) {
                redirect('/admin/login');
            }
            $crud = new grocery_CRUD_extended();

        
            $crud->set_table('users');
            $crud->set_subject('Users');
            $crud->required_fields('salutation','first_name','last_name','username','user_type',
                          'company_type','company_name','company_address1','state','zipcode',
                          'company_email','phone_country_code','phone_number');
            
            
            

            
            
            $crud->columns('first_name','email_id','user_type','company_name');
            $crud->set_relation('parent_id','users','username',array('parent_id' => 0));
            $crud->fields('salutation','first_name','middle_name','last_name','username','email_id','user_type',
                          'company_type','company_name','company_address1','company_address2','state','city','zipcode',
                          'company_email','phone_country_code','phone_number','extension_type','extension_number','is_sms',
                          'parent_id','status');
            
            $crud->set_rules('email_id','Email','valid_email|required');
            $crud->set_rules('compamy_email','Compamy Email','valid_email|required');
            
            $crud->unique_fields('email_id','username');
            
            $crud->display_as('parent_id','Manager Name');
            
            $crud->callback_after_insert(array($this,'GetLatLong_callback'));
            $crud->callback_after_update(array($this,'GetLatLong_callback'));
            
             $crud->callback_after_insert(array($this, 'send_mail'));
            
            $output = $crud->render();
            $output->title = 'Manage Users';
            $output->all_userdata = $this->session->all_userdata();
            $this->load->view('../views/templates/header',$output);
            $this->common->_crud_output($output);
            $this->load->view('../views/templates/footer');
            
        }catch(Exception $e){
            show_error($e->getMessage().' --- '.$e->getTraceAsString());
        }
    }

 

 

When i change the relation like this -> $crud->set_relation('parent_id','users u','username',array('u.parent_id' => 0));

using alias then searching starts working but on add page error comes:

 

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 '., users u.username as s6be37982 FROM (`users` u) WHERE `u`.`parent_id` = 0 AND' at line 1

SELECT users u., users u.username as s6be37982 FROM (`users` u) WHERE `u`.`parent_id` = 0 AND `u`.`parent_id` = 0 ORDER BY `s6be37982`

 

 



#2 Manu Sharma

Manu Sharma

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationIndia

Posted 05 July 2013 - 10:47 AM

Now I'm dong it like this ->

if($crud->getState() != 'add' && $crud->getState() != 'edit')
{
   $crud->set_relation('parent_id','users u','u.username',array('parent_id' => 0));
}
else
{
   $crud->set_relation('parent_id','users','username',array('parent_id' => 0));
}

I think this is not the right way, there should be something else what I must have missed.



#3 davidoster

davidoster

    Grocery CRUD Ninja

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

Posted 05 July 2013 - 11:39 AM

Hello Manu Sharma and welcome to the forums.

Let me understand this correctly,

 

1. you run grocery crud on table users,

$crud->set_table('users');

 

2. then later you set a relation by using the very same table user

$crud->set_relation('parent_id','users','username',array('parent_id' => 0));

 

for what reason exactly? to make the parent_id to get the value of 0?

 

Exactly why do you do this? What are you trying to achieve?

Logically this is wrong. But you need to explain what you are trying to get by this set_relation in order to help you.

 

For your information, the set_relation function is used in order to get infromation from a related table as it says on the manual and not from the same table!


____________________________________________________________

 

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

____________________________________________________________

 

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


#4 Manu Sharma

Manu Sharma

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationIndia

Posted 05 July 2013 - 11:48 AM

Hi,

 

Thanks.

 

I'm managing sub users in same table using parent_id, when parent_id = 0 then its parent user and otherwise it will have primary id of other user from same table as its parent_id so that I can show the parent users in the dropdown from same table while adding more users, thats why i need it, i was doubtful that this way of managing the database is wrong. I should have taken two different tables or is there any better way with which I can sort it out or this db structure is totally unacceptable.

 

Regards,

Manu



#5 davidoster

davidoster

    Grocery CRUD Ninja

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

Posted 05 July 2013 - 12:04 PM

I believe the two (2) tables approach is a better way.


____________________________________________________________

 

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

____________________________________________________________

 

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


#6 Manu Sharma

Manu Sharma

    Newbie

  • Members
  • Pip
  • 5 posts
  • LocationIndia

Posted 05 July 2013 - 12:29 PM

thanks







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users