PDO FETCH_CLASS with joined tables

Question

Let's say I have 2 php objects:

<?php
class Post {
    public $id;
    public $text;
    public $user_id;
}
?>

and

<?php
class User {
    public $id
    public $name
}
?>

Every post has a unique constraint with 1 user in the database.

I want to fill data into the "Post"-object with PDOs "FETCH_CLASS" method which works for all the "Post" attributes but how do I fill the attributes in "User"?

My SQL-statement looks like this:

SELECT post.id, 
       post.text, 
       post.user_id, 
       user.id, 
       user.name 
FROM POST INNER JOIN User on post.user_id = user.id

Thanks!

UPDATE:

ATM I fill my "Post"-class like this:

    $statement = $db -> prepare($query);
    $statement -> execute();
    $statement -> setFetchMode(PDO::FETCH_CLASS, 'Post');
    $posts = $statement -> fetchAll();

So how would I have to change that for also filling the other class "User"?

SOLUTION:

$statement = $db -> prepare($query);
$statement -> execute();
$posts = array();
while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    $post           = new Post();
    $post->id       = $row['post_id'];
    $post->text     = $row['post_text'];
    $post->created  = $row['post_created'];
    $post->image    = $row['post_image'];
    $post->url      = $row['post_url'];
    $post->weight   = $row['post_weight'];
    $post->likes    = $row['post_likes'];
    $user           = new User();
    $user->id       = $row['user_id'];
    $user->nickname = $row['user_nickname'];
    $user->created= $row['user_created'];
    $user->locked   = $row['user_locked'];
    $post->user     = $user;
    $posts[] = $post;
}
return $posts;

Answer 1

You can try using __set method like this:

<?php

include 'connection.php';

class Post {

    public $id;
    public $text;
    public $user;

    public function __construct() {
        $this->user = new User();
    }

    public function __set($name, $value) {

        if (array_key_exists($name, get_object_vars($this->user))) {
            $this->user->$name = $value;
        } else {
            $this->$name = $value;
        }
    }

}

class User {

    public $id;
    public $name;

}

$statement = $pdo->prepare("SELECT * FROM post "
        . "LEFT JOIN user "
        . "ON post.user_id = post.id");
$statement->execute();

$result = $statement->fetchAll(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, Post::class);
echo "<pre>";
var_dump($result);

Answer 2

Theres no support for the directly in PDO as far as I'm aware. Typically if you need to create a complex object graph from the result of query thats the responsibility of an ORM.

If you need this functionality i wold recommend using Doctrine or Propel as opposed to writing something yourself. There are others too that may be lighter weight, but i have no experience with them.

EDIT:

I think maybe i misunderstood the question as im sure others might. I think the real question was how to get access to the joined columns, not cessarially how to create an object from them.

In that case simply using a standard arry fethc method like PDO::FETCH_ASSOC, PDO::FETCH_NUMERIC or PDO::FETCH_BOTH will give you all the columns you queried.

So if you want to turn that into an "object graph" you have to do it manually not by using PDO::FETCH_CLASS.

For example:

//$db is pdo:
// also notice im aliase the columns prefixing the name so that we can tell what belongs to
// post and what belongs to user, an alternative approach would be to use FETCH_NUMERIC,
// which just uses the column positions from the seelct statement as the keys
// so in this case post.id would be in the array as key 0, and user.name would be in the
// array as key 4
$stmt = $db->prepare('SELECT post.id as p_id, 
       post.text as p_text, 
       post.user_id as p_user_id, 
       user.id as u_id, 
       user.name as u_name
FROM POST INNER JOIN User on post.user_id = user.id');

$stmt->execute();

while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
   print_r($row);
   /* will output:
      Array (
         'p_id' => 'value'
         'p_text' => 'value'
         'p_user_id' => 'value'
         'u_id' => 'value',
         'u_name' => 'value'
      )
   So now you need to decide how to create your objects with the information returned
   */
}

Answer 3

Not really a response for the OQ, but because it keeps popping on Google (yes I know its over a year old). You'll find that it is AMAZINGLY faster to just skip loops and query each table separately.

    SELECT post.id, 
           post.text, 
           post.user_id, 
    FROM POST INNER JOIN User on post.user_id = user.id
      $statement = $db -> prepare($query);
        $statement -> execute();
        $statement -> setFetchMode(PDO::FETCH_CLASS, 'Post');
        $posts = $statement -> fetchAll();

    SELECT user.id, 
           user.name 
    FROM POST INNER JOIN User on post.user_id = user.id
      $statement = $db -> prepare($query);
        $statement -> execute();
        $statement -> setFetchMode(PDO::FETCH_CLASS, 'User');
        $users = $statement -> fetchAll();