Codeigniter Active Record JSON对象中的值 [英] Codeigniter Active Record where value in JSON object
问题描述
我有一个问题要问.首先,我有一个表,其中父母的 parent_id
为 0
,而孩子的 parent_id
等于父母的id.所有子代的 parent_id
存储为json编码的数组(一个子记录可以有多个父代).
I have a question to ask. Firstly, I have a table, where the parent has parent_id
is 0
and the child has parent_id
equal parent id. parent_id
of all children are stored as a json encoded array (one child record can have many parents).
因此,当我传递一个父母ID时,如何获得父母的所有孩子.我尝试了一下,但是它不起作用,我也没有任何头绪.
So, how can I get all the children of the parent when I pass one parent id. I tried but it won't work and I don't have a clue.
这是代码:
function get_child_product($parent_id, $limit, $start) {
$this -> db -> from('product');
$this -> db -> where(json_decode('parent_id'), $parent_id);
$this -> db -> limit($limit, $start);
$this -> db -> order_by('order', 'asc');
$this -> db -> order_by('id', 'desc');
$query = $this -> db -> get();
return $query -> result();
}
问题已解决:
function get_child_product($parent_id, $limit, $start) {
$this -> db -> from('product');
$this -> db -> like('parent_id', '"' . $parent_id . '"');
$this -> db -> limit($limit, $start);
$this -> db -> order_by('order', 'asc');
$this -> db -> order_by('id', 'desc');
$query = $this -> db -> get();
return $query -> result();
}
推荐答案
如果我正确理解您的数据库中具有父关系的JSON编码数组,并且您只想获得某个父项的子级.事实是,数据库中的JSON对象只不过是字符串,您无法在查询中动态解码它们并使用where子句.
If I understand correctly you have a JSON encoded array in your database with the parent relationship and you want to get only children of a certain parent. The thing is that JSON objects in a database are nothing more than strings, you cannot dynamically decode them in the query and use a where clause.
您有两个选择:
1.查询所有孩子,然后使用PHP根据解码后的JSON对其进行过滤
1.Query all your children then use PHP to filter them based on the decoded JSON
2.使用mysql like
匹配json格式的字符串
2.Use mysql like
to match a string in json format
function get_child_product($parent_id, $limit, $start) {
return $this -> db -> from('product')
-> like('parent_id', '"parent_id":'.$parent_id)
-> limit($limit, $start)
-> order_by('order', 'asc')
-> order_by('id', 'desc')
-> get()
-> result();
}
请注意, like
参数应与JSON的语法匹配,因此,如果您将id包裹在"
引号中,然后将其添加到参数中
Note that the like
parameter should match the syntax of your JSON so if you're ids are wrapped in "
quotes, then add them to the parameter
这篇关于Codeigniter Active Record JSON对象中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!