使用codeigniter以每个类别的降序获得三个记录 [英] getting three records in descending order of each category using codeigniter
问题描述
我有两个类别,我想获取每个类别的三个记录,后来我找到此链接在此之后我改变了我的DB_Active_rec文件并添加这个代码
I've two categories and I want to fetch three records of each category later I found this link UNION query with codeigniter's active record pattern after this I change my DB_Active_rec file and add this code also
var $unions = array();
public function union_push($table = '') {
if ($table != '') {
$this->_track_aliases($table);
$this->from($table);
}
$sql = $this->_compile_select();
array_push($this->unions, $sql);
$this->_reset_select();
}
public function union_flush() {
$this->unions = array();
}
public function union() {
$sql = '(' . implode(') union (', $this->unions) . ')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
public function union_all() {
$sql = '(' . implode(') union all (', $this->unions) . ')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
然后我创建基于codeigniter函数的查询,如下
and then I create codeigniter's function based query like this
$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$getMedia = $this->db->union_all();
创建此
(SELECT * FROM media m INNER JOIN category c ON
m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
UNION ALL
(SELECT * FROM media m INNER JOIN category c ON
m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
现在它正在获取记录,但不正确我想只使用查询,它显示六个记录第一次查询fetch 3个记录和第二次查询fetch三个记录现在记录是重复的我检查记录的id是6, 5,4和6,5,4。它也可以通过 PHP
来完成,但我想使用查询。提前感谢
Now it is fetching records but not properly I want to use only query, it showing six records first query fetch 3 records and second query fetch three records now records are duplicate I check the id of records it is 6,5,4 and again 6,5,4. It can be done also by PHP
but I want to use query. Thanks in advance
推荐答案
我不知道代码点火器,但基本上你想让它先做联合,然后应用命令在整个集合。这将需要一个子查询。它应该导致以下SQL查询:
I dont know code-igniter, but basicly you want it to do the union first and then apply the order by over the whole set. This would require a subquery. It should result in the following SQL query:
select * from
((SELECT * FROM media m INNER JOIN category c ON m.category_id = c.id )
UNION ALL
(SELECT * FROM media m INNER JOIN category c ON m.category_id = c.id)) T
ORDER BY m.media_files DESC LIMIT 3
希望它能帮助你。
这篇关于使用codeigniter以每个类别的降序获得三个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!