PHP：活动记录表连接 [英] PHP: Active record table joins

问题描述

我有一个使用codeigniter CXTags标记库的应用程序。

数据库结构如下：

帖子

id

/ p>

row_id

表

tag_id

标签

id

safe_tag

标签

我的查询基本上是如果$ safe_tag不是null，然后加入tags_ref on post.id = tags_ref.row_id，在tags_ref.tag_id = tags.id上加入标签，其中tags_ref.table ='posts'和tags.safe_tag ='food'

  SELECT * FROM posts 
 JOIN tags_ref ON posts.id = tags_ref.row_id 
 JOIN标签ON tags_ref.tag_id = tags.id 
 WHERE tags.safe_tag = $ safe_id $不幸的是我在活动记录中写入的查询无法正常工作。b $ b  

 函数get_posts（$ id = NULL， $ safe_tag = NULL）{
 
 if（$ safe_tag！= NULL）{
 echo $ safe_tag; // debugging 
 $ table ='posts'; 
 $ this-> db-> join（'tag_ref'，'posts.id = tags_ref.row_id'）; 
 $ this-> db-> join（'tags'，'tag_ref.tag_id = tags.id'）; 
 $ this-> db-> where（'tags_ref.table'，$ table）; 
 $ this-> db-> where（'tags.safe_tag'，$ safe_tag）; 
} 
 
 //如果提供了ID 
 if（$ id！= NULL）{
 $ this-> db-> where .city_id'，$ id）; 
} 
 
 //执行查询
 $ query = $ this-> db-> get（'posts'）; 
 ... 
  

以下是具有分析功能的查询：

  SELECT * 
 FROM（`posts`）
 INNER JOIN`tags_ref` ON`posts`.`id` =`tags_ref `.`row_id` 
 INNER JOIN`tags` ON`tags_ref`.`tag_id` =`tags`.`id` 
 WHERE`tags_ref`.`table'='posts'
 AND`tags`.`safe_tag` ='food'
 AND`posts`.`city_id` ='2'
  

有人可以看看吗？

解决方案

您忘记在第一个 if {}

  if（$ safe_tag！= NULL）{
 echo $ safe_tag; // debugging 
 $ table ='posts'; 
 $ this-> db-> join（'tag_ref'，'posts.id = tags_ref.row_id'）; 
 $ this-> db-> join（'tags'，'tag_ref.tag_id = tags.id'）; 
 $ this-> db-> where（'tags_ref.table'，$ table）; 
 $ this-> db-> where（'tags.safe_tag'，$ safe_tag）; 
 $ this-> db-> get（）; // here 
} 
  

I have an app that uses the codeigniter CXTags tagging library.

The database structure is as follows:

posts

id

tags_ref

row_id

table

tag_id

tags

id

safe_tag

tag

My query basically goes if $safe_tag is not null then join tags_ref on post.id = tags_ref.row_id, join tags on tags_ref.tag_id = tags.id, where tags_ref.table = 'posts' and tags.safe_tag = 'food'

SELECT * FROM posts 
JOIN tags_ref ON posts.id = tags_ref.row_id
JOIN tags ON tags_ref.tag_id = tags.id
WHERE tags.safe_tag = $safe_id

Unfortunately the query I've written in active record is not functioning properly. The query works perfectly when £safe_tag is null but when it's not I get wrong results.

function get_posts($id = NULL, $safe_tag = NULL) {

    if($safe_tag != NULL){
        echo $safe_tag;//debugging
        $table = 'posts';
        $this->db->join('tags_ref', 'posts.id = tags_ref.row_id');
        $this->db->join('tags', 'tags_ref.tag_id = tags.id');
        $this->db->where('tags_ref.table', $table);
        $this->db->where('tags.safe_tag',$safe_tag);
    }

    //if an id was supplied
    if ( $id != NULL ) {
        $this->db->where('posts.city_id',$id);
    }

    // execute query
    $query = $this->db->get('posts');
    ...

Here is the query with profiling on:

SELECT *
FROM (`posts`)
INNER JOIN `tags_ref` ON `posts`.`id` = `tags_ref`.`row_id`
INNER JOIN `tags` ON `tags_ref`.`tag_id` = `tags`.`id`
WHERE `tags_ref`.`table` = 'posts'
AND `tags`.`safe_tag` = 'food'
AND `posts`.`city_id` = '2' 

Can someone have a look? I think I need a fresh set of eyes on it.

解决方案

Your forgot to actually run the query inside your first if{}

if($safe_tag != NULL){
        echo $safe_tag;//debugging
        $table = 'posts';
        $this->db->join('tags_ref', 'posts.id = tags_ref.row_id');
        $this->db->join('tags', 'tags_ref.tag_id = tags.id');
        $this->db->where('tags_ref.table', $table);
        $this->db->where('tags.safe_tag',$safe_tag);
        $this->db->get(); // here 
    }

这篇关于PHP：活动记录表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！