Zend-db join problem in view script

Questions Components & MVC
,
1

hi there
im a newbie somehow to zend framework and i want to know more about its functionality.
to do so i would like to know how is it possible to join two tables and get the values returned in the view script. besides im using one repository and a file as entity. i joined two tables but i did not get anything returned to my script.
thank you all in advance

2

Could you please explain what you did so far? Code needed too.

1 Like
3

hi dear marco here is the deal
im taking advantage of using mysql as my data resource, i’ve followed the structure of zend framework 3 documentation specifically in blog module:

  • i have one entity object called post.php including codes here:
    namespace Blog\Model;

class Post
{

private $id;
private $text;

/**
 * @var string
 */
private $title;

/**
 * @param string $title
 * @param string $text
 * @param int|null $id
 */
private $description;
private $tarikh;
private $img;
private $visit;
private $short_link;
private $cid;
private $keywords;
private $uid;
private $fname;
private  $lname;
private $ctitle;
public function __construct($title, $text, $id = null,$description,$tarikh,$visit,$short_link,$cid,$keywords,$uid,$img,$fname,$lname,$ctitle)
{
    $this->title = $title;
    $this->text = $text;
    $this->id = $id;
    $this->id = $description;
    $this->tarikh = $tarikh;
    $this->visit = $visit;
    $this->img = $img;
    $this->short_link = $short_link;
    $this->cid = $cid;
    $this->keywords = $keywords;
    $this->uid = $uid;
    $this->fname =$fname;
    $this->lname = $lname;
    $this->ctitle = $ctitle;
}

/**
 * @return int|null
 */
public function getId()
{
    return $this->id;
}

/**
 * @return string
 */
public function getText()
{
    return $this->text;
}

/**
 * @return string
 */
public function getTitle()
{
    return $this->title;
}
public function getDescription()
{
    return $this->description;
}

public function getDate()
{
    return $this->tarikh;
}

public function getVisit()
{
    return $this->visit;
}

public function getUid()
{
    return $this->uid;
}

public function getCid()
{
    return $this->cid;
}

public function getLink()
{
    return $this->short_link;
}

public function getKeywords()
{
    return $this->keywords;
}

public function getImg()
{
    return $this->img;
}
public function getUfname(){
    return $this->fname;
}
public function getUlname (){
    return $this->lname;
}

public function getCtitle (){
    return $this->ctitle;
}

}
they are all the same columns as in the posts table in my database except for ctitle that i have used to implement after joinig tables with posts_category as another table.
here is my repository:
class ZendDbSqlRepository implements PostRepositoryInterface
{
private $db;

/**
 * @param AdapterInterface $db
 */
private $hydrator;

/**
 * @var Post
 */
private $postPrototype;
public function __construct( AdapterInterface $db,
                             HydratorInterface $hydrator,
                             Post $postPrototype)
{
    $this->db = $db;
    $this->hydrator      = $hydrator;
    $this->postPrototype = $postPrototype;
}
/**
 * {@inheritDoc}
 */
public function findAllPosts($paginated=false)
{
    if ($paginated) {
        return $this->fetchPaginatedResults();
    }
    $sql       = new Sql($this->db);
    $sql->select('posts');
    return $sql;
}

private function fetchPaginatedResults()
{
    // Create a new Select object for the table:
    $sql = new Sql($this->db);
    $select = $sql->select('posts');
    $select->join(['c'=>'posts_category'],'c.id = posts.cid',['Ctitle'=>'title'],$select::JOIN_INNER);
    $statement = $sql->prepareStatementForSqlObject($select);
    $result    = $statement->execute();
    if (! $result instanceof ResultInterface || ! $result->isQueryResult()) {
        return [];
    }
    $resultSet = new HydratingResultSet($this->hydrator, $this->postPrototype);
    $resultSet->initialize($result);

    // Create a new result set based on the Album entity:
    //$resultSetPrototype = new ResultSet();
    //$resultSetPrototype->setArrayObjectPrototype(new Post());

    // Create a new pagination adapter object:
    $paginatorAdapter = new DbSelect(
    // our configured select object:
        $select,
        // the adapter to run it against:
    $sql,
        // the result set to hydrate:
        $resultSet
    );
    $paginator = new Paginator($paginatorAdapter);
    return $paginator;
}

}
and after joining and passing the $paginator to the controller as here:
public function indexAction()
{
$paginator = $this->postRepository->findAllPosts(true);
$page = (int) $this->params()->fromQuery(‘page’, 1);
$page = ($page < 1) ? 1 : $page;
$paginator->setCurrentPageNumber($page);

    // Set the number of items per page to 10:
    $paginator->setItemCountPerPage(10);
    return new ViewModel([
        //'posts' => $this->postRepository->findAllPosts(),
        'mostr' => $this->postRepository->findMostRecent(),
        'mostv' => $this->postRepository->findMostVisited(),
        'paginator' => $paginator,
        'postcat' =>$this->post_CategoryRepository->findAllCategory()

    ]);
}

and i pass the “paginator” to the view and use foreach to loop through the object but here as the other methods return their expected values but the getCtitle() method does not seem to return anything and i am dying to know why. thank you for your help.

4

Please check the naming of the property ctitle and the column alias Ctitle.

1 Like
5

thank you so much, that did the trick.