Build array from join select

Question

I have this join select with doctrine:

->select("concat(c.name, ' ', c.second_name) as name_client","c.id as id_client", "p.id as id_project", "s.id as id_status","p.renew_date","p.domain")
                ->from("clients", "c")
                ->leftJoin("c", "projects", "p", "c.id = p.id_client")
                ->leftJoin("c", "todos", "t", "t.id = c.id")
                ->orderBy('p.renew_date', 'ASC');

I need to have a json output like this:

[
 {
   "id":1,
   "name_client" : "Tim",
   "projects" : [
      {
        "id" : 1
        "domain" : "www.test.com"
      },
      {
        "id" : 1
        "domain" : "www.test.com"
      }
    ],
    "todos":  [
      {
        "id" : 1
        "text" : "do something"
      },
      {
        "id" : 1
        "text" : "do something"
      }
    ]
 }
]

How can i loop my data to get this array structure? This is my array data:

Thanks.

With the current query, what output do you get? Can you add it to your question so we know where the discrepancies lie? — Stratus3D
– Stratus3D, Commented Jan 25, 2016 at 17:56

dev35000 · Accepted Answer · 2016-01-25 17:38:11Z

1

can you please display what your "select" returns whith a print_r please, in order to have the data structure ?

Try this :

$clients = array();
foreach ($queryResults as $entry) {
    if (! isset($clients[$entry['id_client']])) {
        $clients[$entry['id_client']] = array(
            'id' => $entry['id_client'],
            'name_client' => $entry['name_client'],
            'todos' => array(),
            'projects' => array()
        );
    }
    if (! empty($entry['id_todo'])) {
        $clients[$entry['id_client']]["todos"][] = array(
            'id' => $entry['id_todo'],
            'text' => $entry['text']
        );
    }
    if (! empty($entry['id_project'])) {
        $clients[$entry['id_client']]["projects"][] = array(
            'id' => $entry['id_project'],
            'domain' => $entry['text']
        );
    }
}

edited Jan 25, 2016 at 17:38

answered Jan 25, 2016 at 16:42

dev35000

1806 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Build array from join select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related