我正在尝试使用codeingniter的活动记录创建一个类似于以下内容的SQL
语句:
SELECT *
FROM (`posts`)
JOIN `Post_images` ON `Post_images`.`post_id` = `posts`.`id`
WHERE `title` LIKE '% $SEARCHTERM %'
OR `content` LIKE '% $SEARCHTERM %'
AND `location` = ' $LOCATION '
GROUP BY `posts`.`id`
我的PHP目前是:
$this->db->like('title', $term);
$this->db->or_like('content', $term);
$this->db->group_by('posts.id');
$this->db->join('Post_images', 'Post_images.post_id = posts.id');
$query = $this->db->get_where('posts', array('location' => $location));
return $query->result_array();
此 PHP 生成的查询为:
SELECT *
FROM (`posts`)
JOIN `Post_images` ON `Post_images`.`post_id` = `posts`.`id`
WHERE `location` = ' $LOCATION '
AND `title` LIKE '% $SEARCHTERM %'
OR `content` LIKE '% $SEARCHTERM %'
GROUP BY `posts`.`id`
如果在"content"列中找到匹配项,则OR
语句位于末尾,因此完全忽略该位置。
有没有办法确保WHERE
语句放在OR
语句之后?
试试这个:
$this->db->like('title', $term);
$this->db->or_like('content', $term);
$this->db->where('location = ', $location);
$this->db->group_by('posts.id');
$this->db->join('Post_images', 'Post_images.post_id = posts.id');
$query = $this->db->get('posts');
return $query->result_array();
这将重现您预期的查询,但我认为您希望这样的事情正常工作:
SELECT *
FROM (`posts`)
JOIN `Post_images` ON `Post_images`.`post_id` = `posts`.`id`
WHERE (`title` LIKE '% $SEARCHTERM %'
OR `content` LIKE '% $SEARCHTERM %')
AND `location` = ' $LOCATION '
GROUP BY `posts`.`id`
注意我添加的括号
若要对 Active Record 执行此操作,需要将string
传递给 where 方法,如下所示:
$this->db->where("(`title` LIKE '% $term %' || `content` LIKE '% $term %')");
$this->db->where('location = ', $location);
$this->db->group_by('posts.id');
$this->db->join('Post_images', 'Post_images.post_id = posts.id');
$query = $this->db->get('posts');
return $query->result_array();
让我知道这是否有效