Codeigniter：无法使用join获取字段值

I have a foreign key Id of third table , eg.(having thirdsubmenu_id of thirdsubmenu table) . i want to get the menu_name name from my parent table mainmenu . Please see below my database table structure for complete details

我有第三个表的外键ID,例如(具有thirdsubmenu表的thirdsubmenu_id)。我想从我的父表mainmenu获取menu_name名称。请参阅下面的数据库表结构以获取完整的详细信息

DATABASE STRUCTURE

 1)Table: mainmenu
    ---------------
     mainmenu_id   PK(primary key)
     menu_name     ..... 

    2)Table: submenu
    -------------------
     submenu_id     PK
     mainmenu_id    FK (foreign key refrences mainmenu table)
     submenu_name   ..... 


    3)Table: thirdsubmenu
    --------------------
      thirdsubmenu_id     PK
      submenu_id          FK (foreign key refrences submenu table)
      thirdsubmenu_name     ........

I tried the below code for getting menu_name from my mainmenu table but i am getting the error.

我尝试了以下代码从我的mainmenu表中获取menu_name,但我收到错误。

 //---------------------------get Main Menu Name by thirdsubmenu_id-----------------------------------
function getMainMenuNameOfSubmenu($thirdsubmenu_id)
{
    $this->load->database();   
    $this->db->select('*');
    $query=$this->db->join('mainmenu', 'mainmenu.mainmenu_id = submenu.mainmenu_id', 'left')
           ->join('submenu', 'submenu.submenu_id = thirdsubmenu.submenu_id', 'left')
           ->get_where('thirdsubmenu',array('thirdsubmenu_id'=>$thirdsubmenu_id));  

    return $query->row('menu_name');   
}

Error I am getting is:

我得到的错误是:

    A Database Error Occurred

    Error Number: 1054

    Unknown column 'submenu.mainmenu_id' in 'on clause'

    SELECT * FROM (`thirdsubmenu`) LEFT JOIN `mainmenu` ON `mainmenu`.`mainmenu_id` = `submenu`.`mainmenu_id` LEFT JOIN `submenu` ON `submenu`.`submenu_id` = `thirdsubmenu`.`submenu_id` WHERE `thirdsubmenu_id` = '17'

    Filename: D:\xampp\htdocs\system\database\DB_driver.php

    Line Number: 330

1 个解决方案

#1

Your approach in creating parent-child relationship is not correct. Consider having 10, 20 or 50 submenus. What would you do? Create a table for each one of them?

您创建父子关系的方法不正确。考虑有10个,20个或50个子菜单。你会怎么做?为每一个创建一个表?

You could hold all the relationship in a single table and just add a column that refers to each menu's parent. Then with a recursive function you could get all the tree of the menu.

您可以将所有关系保存在单个表中,只需添加一个引用每个菜单父级的列。然后使用递归函数,您可以获得菜单的所有树。

Now about your question the problem is that you are joining thirdsubmenu with mainmenu and then you refer to submenu`.`mainmenu_id which is a column from submenu That's why you see the error.

现在关于你的问题,问题是你正在使用mainmenu加入thirdsubmenu然后你引用submenu``mainmenu_id这是一个来自子菜单的列这就是你看错的原因。

Try this code:

试试这段代码:

function getMainMenuNameOfSubmenu($thirdsubmenu_id)
{
    $this->load->database();   
    $this->db->select('*');
    $this->db->from('mainmenu');
    $this->db->join('submenu', 'mainmenu.mainmenu_id = submenu.mainmenu_id', 'left')
           ->join('thirdsubmenu', 'submenu.submenu_id = thirdsubmenu.submenu_id', 'left')
           ->where('thirdsubmenu.thirdsubmenu_id = "' . $thirdsubmenu_id . '"'); 
    $query = $this->db->get();
    return $query->row('menu_name');   
}

#1