Codeigniter Active Record where value in JSON object

Question

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).

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.

Here's the code:

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();
    }

Problem solved:

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();
    }

can you be a little more clear? is the JSON in the database? or are you sending an array of ids to your function to get all the children of those parents? — omma2289
– omma2289, Commented Aug 6, 2013 at 17:28

omma2289 · Accepted Answer · 2013-08-06 17:46:17Z

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.

You have two options:

1.Query all your children then use PHP to filter them based on the decoded 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();
}

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

msanford · Accepted Answer · 2013-08-06 17:19:38Z

1

Are you sure you don't mean

where('parent_id', decoded($parent_id));

?

answered Aug 6, 2013 at 17:19

msanford

12.4k13 gold badges73 silver badges100 bronze badges

Comments

Praz · Accepted Answer · 2013-08-06 17:23:30Z

0

Try where_in clause of active records:

function get_child_product($parent_id, $limit, $start) {
    $this -> db -> from('product');
    $this -> db -> where_in( parent_id, decoded($parent_id) ); #changes here
    $this -> db -> limit($limit, $start);
    $this -> db -> order_by('order', 'asc');
    $this -> db -> order_by('id', 'desc');
    $query = $this -> db -> get();
    return $query -> result();
}

answered Aug 6, 2013 at 17:23

Praz

1

1 Comment

msanford Over a year ago

Missing quotes around 'parent_id'.

Collectives™ on Stack Overflow

Codeigniter Active Record where value in JSON object

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related