{"id":1561,"date":"2022-08-30T15:17:33","date_gmt":"2022-08-30T15:17:33","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/27\/converting-two-simple-sql-queries-into-doctrine-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:17:33","modified_gmt":"2022-08-30T15:17:33","slug":"converting-two-simple-sql-queries-into-doctrine-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/converting-two-simple-sql-queries-into-doctrine-collection-of-common-programming-errors\/","title":{"rendered":"Converting two simple SQL queries into Doctrine-Collection of common programming errors"},"content":{"rendered":"<p>First of all, I want to apologize for the length of this question; I didn&#8217;t know how to properly ask my question without a lot of background. Please bear with me.<\/p>\n<p>I&#8217;m converting a simple application that I use to hone my skills from my own custom database access schema to Doctrine. I chose Doctrine for a number of reasons, not the least of which is that I use it at my day job regularly. I also like how Doctrine is generally a pretty thin (appearing) layer that stays out of the way while still adding a lot of features.<\/p>\n<p>I&#8217;ve converted the data access layer for the <code>users<\/code> table in my database to Doctrine. It&#8217;s very unremarkable (simply getters and setters), except for a few fine details:<\/p>\n<ul>\n<li>I need to have a custom repository for some specific queries and<\/li>\n<li>the <code>User<\/code> object has a default <code>ArrayCollection<\/code> instantiated in the constructor<\/li>\n<\/ul>\n<pre><code>namespace model\\entities;\n\n\/**\n * @Entity(repositoryClass=\"model\\repositories\\UserRepository\")\n * @Table(name=\"users\")\n *\/\nclass User{\n    \/* snip variables *\/\n\n    \/**\n     * @OneToOne(targetEntity=\"Authentication\", mappedBy=\"user\", cascade=\"persist\")\n     *\/\n    private $authentication;\n\n    \/**\n     * @OneToMany(targetEntity=\"Contact\", mappedBy=\"user\", cascade=\"persist\")\n     *\/\n    private $contacts;\n\n    public function __construct() {\n        $this-&gt;contacts = new \\Doctrine\\Common\\Collections\\ArrayCollection();\n    }\n\n    \/* snip getters and setters *\/  \n}\n<\/code><\/pre>\n<p>In my old schema, I had two custom queries that selected a subset of the <code>users<\/code> table.<\/p>\n<p>They are:<\/p>\n<pre><code>public function search( $term = null ){\n    if( !$term ){\n        $sql = \"SELECT *\n                FROM\n                \" . $this-&gt;tableName . \"\n                ORDER BY\n                    lname ASC,\n                    fname ASC\";\n        $res = $this-&gt;db-&gt;q($sql);\n    }\n    else{\n        $sql = \"SELECT *\n                FROM\n                \" . $this-&gt;tableName . \"\n                WHERE\n                    lname LIKE ?\n                    OR fname LIKE ?\n                    OR CONCAT(fname, ' ', lname) LIKE ?\n                ORDER BY\n                    lname ASC,\n                    fname ASC\";\n        $values = array( '%' . $term . '%', '%' . $term . '%', '%' . $term . '%' );\n        $res = $this-&gt;db-&gt;qwv( $sql, $values );\n    }\n\n    return $this-&gt;wrap( $res );\n}\n<\/code><\/pre>\n<p>and:<\/p>\n<pre><code>public function getAllWithRestrictions(){\n    $sql = \"SELECT *\n            FROM\n            \" . $this-&gt;tableName . \"\n            WHERE\n                userid IN\n                (\n                    SELECT\n                        userid\n                    FROM\n                        \" . $this-&gt;uiPre . \"authentications\n                    WHERE\n                        resetPassword = 1\n                        OR disabled = 1\n                )\";\n    $res = $this-&gt;db-&gt;q( $sql );\n\n    return $this-&gt;wrap($res);\n}\n<\/code><\/pre>\n<p>where <code>$this-&gt;db<\/code> is a thin PHP PDO wrapper and <code>$this-&gt;wrap<\/code> does magic with zero\/single\/multiple rows returned and converting them into data objects.<\/p>\n<p>Now, I figured this would be very easy to convert to Doctrine. In the case of <code>getAllWithRestrictions<\/code> it&#8217;s simply a <code>-&gt;where<\/code>, <code>-&gt;orWhere<\/code> set, right? I don&#8217;t know anymore.<\/p>\n<p>I found these Stackoverflow questions that I used to try to construct my queries, but I&#8217;m running into error after error, and I&#8217;m not sure how far down the rabbit hole I need to go:<\/p>\n<p>SQL Multiple sorting and grouping<br \/>\nOrder by multiple columns with Doctrine<br \/>\nDoctrine: Multiple (whereIn OR whereIn) query?<br \/>\nDoctrine &#8211; or where?<\/p>\n<p>My custom repository currently looks like this, but I can&#8217;t say it&#8217;s even close to correct as I&#8217;ve been fiddling with it for a long time, and it&#8217;s just a hodge-podge of what I thought might work:<\/p>\n<p id=\"rop\"><small>Originally posted 2013-11-27 11:51:43. <\/small><\/p>","protected":false},"excerpt":{"rendered":"<p>First of all, I want to apologize for the length of this question; I didn&#8217;t know how to properly ask my question without a lot of background. Please bear with me. I&#8217;m converting a simple application that I use to hone my skills from my own custom database access schema to Doctrine. I chose Doctrine [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1561","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=1561"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1561\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=1561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=1561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=1561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}