以数据映射器模式创建对象数组

Question

EDIT: OUTPUT CODE AT BOTTOM OF QUESTION

I just posted a question, thinking my problem was the query, but it turns out it's my PHP code.

Here's the problem. I have a GoalChallenge class, which has numerous properties, one of which should be one, or an array of ProductService objects; see GoalChallenge class below (note I have stripped out the other getters and setters, and left the ones that relate to the ProductService class.

When I use GoalChallenge::findByPersonaId, a ProductService object is created and relates to the matching GoalChallenge object, but there should be 2 ProductService objects within the GoalChallenge->product_service property (the query should match 2 rows). Instead, a duplicate GoalChallenge object is created, containing the same property values for everything other than the product_service property, which contains the 2nd matching object from the query.

I need the two matching ProductService objects to be part of the same GoalChallenge object (as matched by the query) - how can I make this happen?

If you need anything else, please ask. Really appreciate any help! Code below;

GoalChallenge.class.php

<?php

class GoalChallenge
{
    private $id;
    private $persona_id;
    private $title;
    private $item_category;
    private $description;
    private $solution;
    private $product_service;
    private $research_checklist;
    private $subtopics;
    private $keywords;
    private $status;


    public function __construct(
        $id = null, 
        $persona_id = null, 
        $title = null, 
        $item_category = null, 
        $description = null,
        $solution = null,
        ProductService $product_service = null,
        $research_checklist = null,
        $subtopics = null, 
        $keywords = null, 
        $status = null
    ) {
        $this->id = $id;
        $this->persona_id = $persona_id;
        $this->title = $title;
        $this->item_category = $item_category;
        $this->description = $description;
        $this->solution = $solution;
        $this->product_service = $product_service;
        $this->research_checklist = $research_checklist;
        $this->subtopics = $subtopics;
        $this->keywords = $keywords;
        $this->status = $status;
    }

    public function getProductService()
    {
        return $this->product_service;
    }

    public function setProductService(ProductService $product_service)
    {
        $this->product_service = $product_service;
    }

}

And my GoalChallengeMapper.class.php;

class GoalChallengeMapper
{

    protected $dblayer;

    public function __construct(PDO $dblayer)
    {
        $this->dblayer = $dblayer;
    }

    public function saveField($id, $field, $data)
    {
        try {
            $this->dblayer->beginTransaction();
            $stmt = $this->dblayer->prepare("UPDATE goals_challenges SET $field = :data WHERE id = :id");
            $stmt->bindParam(':id', $id);
            $stmt->bindParam(':data', $data);
            $stmt->execute();

            $this->dblayer->commit();

            return $stmt->rowCount();

        } catch(PDOException $e) {
            $this->dblayer->rollBack();
            echo $e->getMessage();
            exit;
        }

    }

    public function findByPersonaId($persona_id)
    {
        try {
            $this->dblayer->beginTransaction();
            $stmt = $this->dblayer->prepare("SELECT goals_challenges.*, products_services.id as psid, products_services.url, products_services.feature_benefit from goals_challenges LEFT JOIN products_services ON goals_challenges.id = products_services.goal_challenge_id WHERE goals_challenges.persona_id = :persona_id");
            $stmt->bindParam(':persona_id', $persona_id);
            $stmt->execute();

            $this->dblayer->commit();

            $result_set = array();

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $result_set[] = $this->mapObject($row); 
            }

            return $result_set;

        } catch (PDOException $e) {
            $this->dblayer->rollBack();
            echo $e->getMessage();
            exit;
        }
    }

    public function mapObject(array $row)
    {
        $entry = new GoalChallenge();
        $entry->setId($row['id']);
        $entry->setPersonaId($row['persona_id']);
        $entry->setTitle($row['title']);
        $entry->setItemCategory($row['item_category']);
        $entry->setDescription($row['description']);
        $entry->setSolution($row['solution']);
        $entry->setProductService(new ProductService($row['psid'], $row['id'], $row['url'], explode(',', $row['feature_benefit'])));
        $entry->SetResearchChecklist($row['research_checklist']);
        $entry->setSubtopics($row['subtopics']);
        $entry->setKeywords($row['keywords']);
        $entry->setStatus($row['status']);

        return $entry;
    }
}

And finally, my ProductService class (minus getters and setters)

class ProductService
{
    private $id;
    private $goal_challenge_id;
    private $url;
    private $feature_benefit = [];

    public function __construct($id = null, $goal_challenge_id = null, $url = null, array $feature_benefit = null)
    {   
        $this->id = $id;
        $this->goal_challenge_id = $goal_challenge_id;
        $this->url = $url;
        $this->feature_benefit = $feature_benefit;
    }
}

THIS IS THE OUTPUT

GoalChallenge Object
(
    [id:GoalChallenge:private] => 173
    [persona_id:GoalChallenge:private] => 14
    [title:GoalChallenge:private] => Lead Gen
    [item_category:GoalChallenge:private] => Business Challenge
    [description:GoalChallenge:private] => 


    [solution:GoalChallenge:private] => Advertising
    [product_service:GoalChallenge:private] => ProductService Object
        (
            [id:ProductService:private] => 1
            [goal_challenge_id:ProductService:private] => 173
            [url:ProductService:private] => www.google.com
            [feature_benefit:ProductService:private] => Array
                (
                    [0] => good for testing
                    [1] =>  mobile
                )

        )

    [research_checklist:GoalChallenge:private] => 0,0,0,0,0,0
    [subtopics:GoalChallenge:private] => 
    [keywords:GoalChallenge:private] => ,,,,
    [status:GoalChallenge:private] => 1
)

GoalChallenge Object
(
    [id:GoalChallenge:private] => 173
    [persona_id:GoalChallenge:private] => 14
    [title:GoalChallenge:private] => Lead Gen
    [item_category:GoalChallenge:private] => Business Challenge
    [description:GoalChallenge:private] => 


    [solution:GoalChallenge:private] => Advertising
    [product_service:GoalChallenge:private] => ProductService Object
        (
            [id:ProductService:private] => 3
            [goal_challenge_id:ProductService:private] => 173
            [url:ProductService:private] => www.test.com
            [feature_benefit:ProductService:private] => Array
                (
                    [0] => good for searching
                    [1] =>  well known
                )

        )

    [research_checklist:GoalChallenge:private] => 0,0,0,0,0,0
    [subtopics:GoalChallenge:private] => 
    [keywords:GoalChallenge:private] => ,,,,
    [status:GoalChallenge:private] => 1
)

mysql> SELECT goals_challenges.*, products_services.id as psid, products_services.url, products_services.feature_benefit FROM goals_challenges LEFT JOIN products_services ON goals_challenges.id = products_services.goal_challenge_id WHERE goals_challenges.persona_id = 14;

+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+
| id  | persona_id | title    | item_category      | description | solution    | product_service | research_checklist | subtopics | keywords | status | psid | url            | feature_benefit                |
+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+
| 173 |         14 | Lead Gen | Business Challenge |             | Advertising | NULL            | 0,0,0,0,0,0        | NULL      | ,,,,     |      1 |    1 | www.google.com | good for testing, mobile       |


| 173 |         14 | Lead Gen | Business Challenge |             | Advertising | NULL            | 0,0,0,0,0,0        | NULL      | ,,,,     |      1 |    3 | www.test.com   | good for searching, well known |


+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+

2 rows in set (0.00 sec)

print_r($goals_challenges)

Array
(
    [173] => Array
        (
            [id] => 173
            [persona_id] => 14
            [title] => Lead Gen
            [item_category] => Business Challenge
            [description] => 


            [solution] => Advertising
            [research_checklist] => 0,0,0,0,0,0
            [subtopics] => 
            [keywords] => ,,,,
            [status] => 1
            [psid] => 1
            [url] => www.google.com
            [feature_benefit] => good for testing, mobile
            [product_services] => Array
                (
                    [0] => Array
                        (
                            [0] => 1
                            [1] => www.google.com
                            [2] => good for testing, mobile
                        )

                    [1] => Array
                        (
                            [0] => 3
                            [1] => www.test.com
                            [2] => good for searching, well known
                        )

                )

        )

)

Answer 1

As suspected, the JOIN query's result set requires a little more logic to format the way you want than you have given it. A SQL result set is always a 2 dimensional structure, even when the data it contains have more complicated relationships (like your one-to-many relationship).

There are a few ways to approach this, and the one I think will be closest to your existing pattern is to change the way you fetch rows a little. Instead of fetching a row then immediately mapping it, build some logic into the fetch loop to create the nested structure your join expresses, wherein ProductService is an array of one or more objects. Then you'll be able to modify the mapObject() method to handle an array of nested ProductService objects.

So instead of mapping as you fetch, create an array onto which the fetched rows are appended. On each iteration, you must check if the common values (those of GoalChallenge) have changed. If not, you continue building an array for ProductService. If they have changed (like if your query returns more than one different GoalChallenge) you start a new outer structure.

$result_set = array();

// Temp variable to remember what goals_challenges.id is being grouped
$current_id = null;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Create a new structure if the id changed
    if($row['id'] !== $current_id) {
       $current_id = $row['id'];
       // Store a new row for goal_challenges, holding all 
       // the common columns in its outer structure
       $goal_challenges[$row['id']] = $row;
       // and it has a sub-array for product services
       $goal_challenges[$row['id']]['product_servies'] = array();
    }
    // Append the product_services columns as an array onto the subarray
    $goal_challenges[$row['id']]['product_services'][] = array('psid'=>$row['psid'], 'url'=>$row['url'], 'feature_benefit'=>$row['feature_benefit']);
}

// Now you can pass each row of the $goal_challenges array
// to mapObject. There should be only one row, but if you 
// use the same pattern for queries that return many rows it
// will work without much modification
$result_set = array();
foreach ($goal_challenges as $gc) {
    $result_set[] = $this->mapObject($gc);
}
// Return the array of results (which probably has only one element)
return $result_set;

Okay, that should fix the fetch pattern to do what you need. The other issue is to make the mapObject() method handle the inner array of product services. That's easy enough with a loop.

public function mapObject(array $row)
{
    $entry = new GoalChallenge();
    $entry->setId($row['id']);
    $entry->setPersonaId($row['persona_id']);
    $entry->setTitle($row['title']);
    $entry->setItemCategory($row['item_category']);
    $entry->setDescription($row['description']);
    $entry->setSolution($row['solution']);
    $entry->SetResearchChecklist($row['research_checklist']);
    $entry->setSubtopics($row['subtopics']);
    $entry->setKeywords($row['keywords']);
    $entry->setStatus($row['status']);

    // Create ProductService objects for each item in the sub-array
    foreach ($row['product_services'] as $ps) {
        $entry->setProductService(new ProductService($ps['psid'], $row['id'], $ps['url'], explode(',', $ps['feature_benefit'])));
    }

    return $entry;
}

And finally, make the setProductService() method append to an array instead of setting a single property:

public function setProductService(ProductService $product_service)
{
    // Append onto an array
    $this->product_service[] = $product_service;
}

In the GoalChallenge::__construct() parameters, make it accept and default an array instead of a single ProductService object, changing to $product_service = array()

So this is all sort of complicated, and it speaks to why pre-built ORM libraries like Doctrine are commonly used. This logic is abstracted away for you in an easily reusable way. PDO does have a FETCH_GROUP methodology, but it is meant to group just one column (like the id) as an outer array key and all other columns as sub-arrays. Your situation is such that most columns belong at an outer level, with only those relevant to the joined ProductService as an inner sub-array, so that doesn't really work.