SQL Join in ZF3 Expressive - ResultSet does not give joined Columns

Question

I'm trying to get a title out of a second table (by left-join) but my resultSet isn't delivering the column of the joined table.

This is how my TableGateway looks like:

 public function fetchCourseListWithStudyprogram()
{
        $select = $this->getSql()->select();
        $select->where->equalTo('course_active', 'Y');
        $select->join('studyprogram', 'studyprogram.studyprogram_id = course.studyprogram_id', ['studyprogram_title'], $select::JOIN_LEFT);

    $resultSet = $this->selectWith($select);
    var_dump($resultSet);
    return $resultSet->toArray();
}

The statement is getting constructed as it should be:

public 'queryString' => string 'SELECT `course`.*, `studyprogram`.`studyprogram_title` AS `studyprogram_title` FROM `course` LEFT JOIN `studyprogram` ON `studyprogram`.`studyprogram_id` = `course`.`studyprogram_id` WHERE `course_active` = :where1' (length=214)

When it comes to getting results, it ignores the "studyprogram_title" column, I guess its because of the given resultSet (with binded CourseEntity)

Is there any way to show the studyprogram_title without adding it to the CourseEntity? Its not really a part of it so I guess its dirty if I add it there.

score 0 · Answer 1 · answered Jul 04 '17 at 20:13

There is a way how to handle two tables in a model.

If you want to join two tables directly without specifying tablegateway for each table then that will raise an issue. That means you can not make the resultset to an array because, in this case, you are not using the resultsetprototype for the other table as you do for the one (the current model) where you are trying to join two tables.

You may check out this answer!

SQL Join in ZF3 Expressive - ResultSet does not give joined Columns

1 Answers1