How to map GROUP_CONCAT with pdo->fetchAll and mode PDO::FETCH_CLASS from string to an array

Question

I was wondering how fetchAll of PDO is actually implemented to get an Idea how to map the result from the database including a GROUP_CONCAT() comma separated list string to an array property.

Having a sql like

$query = "Select a.id, GROUP_CONCAT(b.name) AS referencingNames FROM a JOIN b on (a.id = b.id_a)"

Will return something like

id (int)	referencingNames (srting)
1	Mark, Mona, Sam
2	Jim, Tom, Sara, Mike
3	...

My Object to map to looks like this

class someObject {
  public int $id;
  public array $referencingNames;
}

When I call my php code then:

 $pdo = new PDO(....)
 $statement = $pdo->prepare($query);
 $statement->execute();

 $objects = $statement->fetchAll(PDO::FETCH_CLASS, someObject::class);

I am running into a type error, as referencingNames is obviously a string.

What I then tried was to set $referencingNames private and use the magic function __set() as it says in the php docs the following

is run when writing data to inaccessible (protected or private) or non-existing properties

class someObject {
  public int $id;
  private string $referencingNames;

  public ?array $refNamesList;

  public function __set($name, $value) 
  {
    if($name == "referencingNames") {
        $this->referencingNames = $value;
        $this->refNamesList = explode(",", $value);
    } else {
        $this->$name = $value;
    }
  } 
}

The bad news: this didn't work out. I get back an object where refNamesList stays null. Logging the call of __set() did not give me any output, so I assume, it does not get called.

Has anybody an Idea how I can map GROUP_CONCAT to an array with PDOs fetchAll() without building my own solution? I mean, fetching all, and iterating the whole thing is still an option, but I was wondering if I can do this more elegantly anyhow.

score 3 · Accepted Answer · answered May 24 '21 at 15:09

3

As the name of the column you are loading is part of the class, it's setting that value anyway without having to call the __set method. So one way (seems to work) is to add a column alias which doesn't exist in the class - nameList in this example...

$query = "Select a.id, GROUP_CONCAT(b.name) AS nameList 
             FROM a 
             JOIN b on (a.id = b.id_a)"

this should then call the __set method and you can process when you get nameList...

class someObject {
  public int $id;
  public array $referencingNames;

  public function __set($name, $value)
  {
    if($name == "nameList") {
        $this->referencingNames = explode(",", $value);
    } else {
        $this->$name = $value;
    }
  }
}

answered May 24 '21 at 15:09

Nigel Ren

56,122
11
43
55

So the php doc is wrong here? As set seems not to be called for the private property? – helle May 24 '21 at 15:33
1

@helle, the use of FETCH_CLASS seems to bypass the access modifier. This makes sense when most classes would use private data and want FETCH_CLASS to still be able to populate them properly. – Nigel Ren May 24 '21 at 15:35
Thanks for the hint. I will try it out later and accept the answer. Great to have a solution – helle May 24 '21 at 15:53
Nice !! This worked really good and saves me now lots of time! – helle May 24 '21 at 17:12

How to map GROUP_CONCAT with pdo->fetchAll and mode PDO::FETCH_CLASS from string to an array

1 Answers1