Zend-db vs. doctrine

Contributors
9

I’ll gladly counter that. Take https://github.com/phpsrbija/unfinished/blob/7f691bb332539f5e53b738474947af4a42cf0356/application/packages/Category/src/Mapper/CategoryMapper.php#L47-L63 for example (first query I found - they seem to be all in mappers):

    public function getCategoryPostsSelect($categoryId = null, $limit = null)
    {
        $select = $this->getSql()->select()
            ->columns(['category_name' => 'name', 'category_slug' => 'slug'])
            ->join('articles', 'articles.category_uuid = category.category_uuid',
                ['article_id', 'slug', 'admin_user_uuid', 'published_at']
            )->join('article_posts', 'article_posts.article_uuid = articles.article_uuid', ['*'], 'right')
            ->join('admin_users', 'admin_users.admin_user_uuid = articles.admin_user_uuid',
                ['admin_user_id', 'first_name', 'last_name', 'face_img']
            )->where(['articles.status' => 1])
            ->order(['published_at' => 'desc']);
        if ($categoryId) {
            $select->where(['category_id' => $categoryId]);
        }
        if ($limit) {
            $select->limit($limit);
        }
        return $select;
    }

The first problem here is that you expose a Zend\Db\Sql\Select, which then flies across the system and can be arbitrarily modified. Yes, this used to be very common, but also problematic due to complex queries being programmatically built and then executed, and this leads to extremely complex queries.

Second, this thing can be written as:

SELECT
    c.category_name AS name,
    c.category_slug AS slug
FROM
    category c
JOIN
    articles a
    WHERE a.category_uuid = c.category_uuid
RIGHT JOIN
    article_posts p
    WHERE p.article_uuid = a.article_uuid
JOIN
    admin_users au
    WHERE au.admin_user_uuid = a.admin_user_uuid
WHERE
    a.status = 1 -- yay magic numbers?
    AND (c.categoryId = :categoryId OR 1 = :noCategoryId)
ORDER BY
    published_at DESC

In DQL (doctrine’s language), this would probably not work due to the right join (which confuses the heck out of me BTW). I don’t know the domain, so I don’t know how to translate it, but it would probably look like this IF done through the ORM:

SELECT
    c
FROM
    MyNamespace\Posts p
LEFT JOIN
    p.article a
LEFT JOIN
    a.category c
LEFT JOIN
    MyNamespace\AdminUser au WITH au.userId = a.user
WHERE
    a.status = 1
    AND (c.categoryId = :categoryId OR 1 = :noCategoryId)

I think the SQL version beats both examples, and makes things clearer to the reader. Also, I can copy-paste it in and figure things out without having to run the application and figure out (later) what the heck will hit the DB.

And then the entities end up like this: https://github.com/phpsrbija/unfinished/blob/7f691bb332539f5e53b738474947af4a42cf0356/application/packages/Page/src/Entity/Page.php#L7-L267

/**
 * Class Page.
 */
class Page
{
    private $page_uuid;
    private $page_id;
    private $title;
    private $body;
    private $description;
    private $main_img;
    private $has_layout;
    private $is_homepage;
    private $is_active;
    private $created_at;
    private $slug;
    private $is_wysiwyg_editor;
    /**
     * @return mixed
     */
    public function getIsWysiwygEditor()
    {
        return $this->is_wysiwyg_editor;
    }
    /**
     * @param mixed $is_wysiwyg_editor
     */
    public function setIsWysiwygEditor($is_wysiwyg_editor)
    {
        $this->is_wysiwyg_editor = $is_wysiwyg_editor;
    }

That’s just setter/getter mess in order to please the hydrator. Can do manual hydration or hydration via reflection, but you end up mostly with 1:1 what is in the DB record, which is also not really useful, since your objects should have behavior (see https://github.com/symfony/symfony-docs/issues/8893 for a fun discussion about that).

1 Like