CODEWITHSUNDEEP
jsonpostgresqlcodeignitercodeigniter-query-builder
g-ulrich
g-ulrich

Reputation: 51

Codeigniter Postgres JSON array of selected rows from a joined table

Note: I edited the question to better reflect what I'm after. I suspect I need a subquery inside the json_agg that outputs date objects grouped by date but I have no idea how to do that.

I have 3 tables

Taggers are a small subset of the users I want. Each tagger will have multiple rows of assets assigned to them.

I am trying to get an array of taggers, each having groups of dates that have an array of tasks.

Array
(
    [id] => 1
    [userid] => 519560
    [timezone] => -7
    [fname] => Gregory
    [status] => 99
    [assigned_titles] => Array (
        [{"202307" : [ [1, 1, 1],[777, 1, 0]]}],
        [{"202306" : [[2, 1, 1], [543, 0, 1]]}]
      )
)

So assigned_titles should be an array of objects with a date_code key. The value of each object should be an array of arrays.

I want to access this data on a loop as such:

foreach($output_array as $o) {
  // access the outer data. Here the order doesn't matter
  for($i = 0; $i <=12; $i++  ) {
    // each date is a separate group. I want the past 12 months, in order. 
    foreach($o['assigned_titles][date("Ym",strtotime("-$i month"))] as $title) {
      // here the order doesn't matter
    }
  }
}

I tried the following but the assigned_titles is not grouped by date. I get this:

[assigned_titles] => [{"202307" : [1, 1, 1]}, {"202306" : [2, 1, 1]}, {"202307" : [777, 1, 1]}]

$query = 'json_agg( 
  json_build_object(
    frl_tagger_assets.date_code, json_build_array(
      frl_tagger_assets.title_id,frl_tagger_assets.breakdown,frl_tagger_assets.breakdown
      )
    ) 
  )  as assigned_titles ';


$this->db
  ->select('frl_taggers.*, frl_users.userid, frl_users.fname, frl_users.lname, frl_users.status, '.$query)
  ->from('frl_taggers')
  ->join('frl_tagger_assets', 'frl_taggers.userid = frl_tagger_assets.userid', 'left')
  ->join('frl_users', 'frl_taggers.userid = frl_users.userid', 'left')
  ->where('frl_users.status >= 80',NULL, FALSE )
  ->where('date_code >='.date("Ym",strtotime("-1 month")), NULL, FALSE)
  ->group_by('frl_taggers.id')
  ->group_by('frl_users.userid');
$query = $this->db->get();
$res = $query->result_array();

Upvotes: 0

Views: 74

Answers (1)

Liam
Liam

Reputation: 20950

The ( and ) in your json_agg(...) are probably being escaped (which you do not want). Check the documentation for the select() function for whichever version of CodeIgniter you are using.

From the CodeIgniter 4 docs:

select() accepts an optional second parameter. If you set it to false, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.

Upvotes: 0

Related Questions