{"id":1683,"date":"2022-08-30T15:18:34","date_gmt":"2022-08-30T15:18:34","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/27\/problem-about-dql-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:18:34","modified_gmt":"2022-08-30T15:18:34","slug":"problem-about-dql-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/problem-about-dql-collection-of-common-programming-errors\/","title":{"rendered":"problem about dql-Collection of common programming errors"},"content":{"rendered":"<ul>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/8361ba9354d54301d67cf2d0ded0f504?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nrockerest<br \/>\nphp mysql doctrine2 dql<br \/>\nFirst 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 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<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/eedd5a37effb5bdd4bbd71ea86aef617?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nMohit Jain<br \/>\nmysql doctrine dql<br \/>\nSuppose there are two tables.Table X&#8211; Columns:id x_valueTable Y&#8211; Columns:id x_id y_valueNow I dont want to define relationship in doctrine classes and i want to retrieve some records using these two tables using a query like this:Select x_value from x, y where y.id=&#8221;variable_z&#8221; and x.id=y.x_id;I m not able to figure out how to write query like this in doctrine ormEDIT:Table structures:Table 1:CREATE TABLE IF NOT EXISTS `image` (`id` int(11) NOT NULL AUTO_INCREMENT, `random<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/087e2917e5f85877771e1880e4e6ad3b?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\ncalumbrodie<br \/>\nphp doctrine dql doctrine-1.2<br \/>\nI am trying to perform a simple join on two tables and am having trouble finding the right syntax to use &#8211; I know i am missing something simple.From the &#8216;users&#8217; table i need the id (working fine) and from the &#8216;meta&#8217; table i need the last_name (not working)$q = Doctrine_Query::create() -&gt;from(&#8216;Users u&#8217;) -&gt;leftJoin(&#8216;u.Meta m&#8217;);$users = $q-&gt;execute();\/\/attempt 1 foreach($users as $user){$user_array[$user-&gt;id] = $user-&gt;last_name; }\/\/attempt 2 foreach($users as $user){$user_arra<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/i.stack.imgur.com\/p1Ere.jpg?s=32&amp;g=1\" \/><br \/>\nJimena<br \/>\ncodeigniter join orm doctrine dql<br \/>\ni have 2 tables: &#8220;User&#8221; and &#8220;States&#8221;. What i pretend to do, is to count how many users are from certain state, ie:state total users santa fe 5 buenos aires 20and so on.I&#8217;m using codeigniter with doctrine, here&#8217;s my code:public function countByState(){$this-&gt;qb = $this-&gt;em-&gt;createQueryBuilder();$this-&gt;qb-&gt;select(&#8216;s.state_id&#8217;, $this-&gt;qb-&gt;expr()-&gt;count(&#8216;u.state&#8217;))-&gt;from(&#8216;models\\States&#8217;, &#8216;s&#8217;)-&gt;leftJoin(&#8216;s.state_id&#8217; , &#8216;u&#8217;)-&gt;leftJoin(&#8216;models\\User&#8217;, &#8216;u&#8217;)-&amp;<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/087e2917e5f85877771e1880e4e6ad3b?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\ncalumbrodie<br \/>\nescaping doctrine2 dql reserved-words<br \/>\nUsing Doctrine 2.I have an entity called &#8216;size&#8217; and I&#8217;m trying to form some DQL (using the QueryBuilder) to pull these entities from the database.It looks like &#8216;Size&#8217; is a reserved word http:\/\/www.doctrine-project.org\/docs\/orm\/2.0\/en\/reference\/dql-doctrine-query-language.html#id3I&#8217;m unable to find a way to escape the entity name (I&#8217;ve tried backticks and double quotes)$dql = &#8220;SELECT product p join p.size size&#8221;;Executing the above results in:Fatal error: Uncaught exception &#8216;Doctrine\\ORM\\Query\\Que<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/a8f7407e48e3411ddacaee8dfb60dad6?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nDan Lee<br \/>\nphp doctrine2 sql-order-by version dql<br \/>\nI have a table that holds version numbers and I want to read them out naturally sorted. This is no problem with raw SQL:SELECT * FROM versions ORDER BY REPLACE(version, &#8216;.&#8217;, &#8221;)+0 DESCBut if I write my query in Doctrine2 like this:$qry = $this-&gt;createQueryBuilder(&#8216;v&#8217;)-&gt;select(&#8216;v&#8217;)-&gt;orderBy(&#8220;REPLACE(v.version, &#8216;.&#8217;, &#8221;)+0&#8243;, &#8216;DESC&#8217;);I only getFatal error: Uncaught exception &#8216;Doctrine\\ORM\\Query\\QueryException&#8217; with message &#8216;[Syntax Error] line 0, col 100: Error: Expected end of string, got<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/5e5fbc655f911e96e8bea5ba4c4c7d16?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nTooTiredToDrink<br \/>\nphp symfony2 doctrine dql<br \/>\nI have been trying to pull out a random row, I have used this: This is the example code I found which did not really help ( I found here: https:\/\/gist.github.com\/pierroweb\/1518601)class QuestionRepository extends EntityRepository {public function findOneRandom(){$em = $this-&gt;getEntityManager();$max = $em-&gt;createQuery(&#8216;SELECT MAX(q.id) FROM EnzimQuestionBundle:Question q&#8217;)-&gt;getSingleScalarResult();return $em-&gt;createQuery(&#8216;SELECT q FROM EnzimQuestionBundle:Question q WHERE q.id &gt;= :<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/i.stack.imgur.com\/j0ESL.gif?s=32&amp;g=1\" \/><br \/>\nfoozy<br \/>\norm doctrine2 dql<br \/>\nI have following Doctrine 2 STI Vehicle entity which have two child classes named Car and Bike need to count items in vehicles table by vehicle type. (For example; summary of the table: 3 cars, 5 bikes..)\/*** @ORM\\Entity* @ORM\\InheritanceType(&#8220;SINGLE_TABLE&#8221;)* @ORM\\DiscriminatorColumn(name=&#8221;type&#8221;, type=&#8221;string&#8221;)* @ORM\\DiscriminatorMap({&#8220;V&#8221; = &#8220;Vehicle&#8221;, &#8220;C&#8221; = &#8220;CarEntity&#8221;, &#8220;B&#8221; = &#8220;BikeEntity&#8221;})* @ORM\\Table(name=&#8221;vehicles&#8221;, uniqueConstraints={@ORM\\UniqueConstraint(name=&#8221;discr_type_name&#8221;,columns={&#8220;typ<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/5e5fbc655f911e96e8bea5ba4c4c7d16?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nTooTiredToDrink<br \/>\nphp symfony2 doctrine dql<br \/>\nThis is my query for getting a random product:public function getRelatedProducts(){$em = $this-&gt;getDoctrine()-&gt;getManager();$max = $em-&gt;createQuery(&#8216;SELECT MAX(p.id) FROM GlassShopBundle:Product p&#8217;)-&gt;getSingleScalarResult();return $em-&gt;createQuery(&#8216;SELECT q FROM GreenMonkeyDevGlassShopBundle:Product p WHERE p.id &gt;= :rand ORDER BY p.id ASC&#8217;)-&gt;setParameter(&#8216;rand&#8217;,rand(0,$max))-&gt;setMaxResults(1)-&gt;getSingleResult()-&gt;getResults(); }I get a Undefined method &#8216;getRelatedByC<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/i.stack.imgur.com\/9up6P.jpg?s=32&amp;g=1\" \/><br \/>\nMarko Jovanovic<br \/>\nsql symfony2 group-by doctrine2 dql<br \/>\nI&#8217;m trying to translate this (My)SQL to DQLSELECT content, created, AVG(rating) FROM point GROUP BY DAY(created) ORDER BY created ASCAnd I&#8217;m stuck at GROUP BY part, apparently DAY\/WEEK\/MONTH isn&#8217;t recognized as valid &#8220;function&#8221;.[Semantical Error] line 0, col 80 near &#8216;(p.created) ORDER&#8217;: Error: Cannot group by undefined identification variable.$this-&gt;createQueryBuilder(&#8216;p&#8217;)-&gt;select(&#8216;p&#8217;)-&gt;groupBy(&#8216;DAY(p.created)&#8217;)-&gt;orderBy(&#8216;p.created&#8217;, &#8216;ASC&#8217;)Q: Is it possible to create this kind of que<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/eedd5a37effb5bdd4bbd71ea86aef617?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nMohit Jain<br \/>\nphp doctrine dql<br \/>\nI am firing a update query. Its working for one page and not working on other one. Can anybody take a look. thanks&lt;br \/&gt;&lt;b&gt;Fatal error&lt;\/b&gt;: Uncaught exception &#8216;Doctrine_Connection_Mysql_Exception&#8217; with message &#8216;SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens&#8217; in C:\\xampp\\htdocs\\fanyer\\doctrine\\lib\\Doctrine\\Connection.php:1084Stack trace: #0 C:\\xampp\\htdocs\\fanyer\\doctrine\\lib\\Doctrine\\Connection\\Statement.php(253):<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/3ad293cfe155e2311cad6579fed7ec6f?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nh3g0r_<br \/>\ndoctrine dql<br \/>\nI tried to generate a simple SQL select:SELECT c.com_id, c.pro_id, c.com_nombre FROM bd_fn.fn_comuna c inner join bd_fn.fn_provincia p on (c.pro_id = p.pro_id) where p.pro_nombre = &#8216;namepro&#8217;;But the DQL throw this error:Doctrine_Table_Exception&#8217; with message &#8216;Unknown relation alias fn_provincia. The doctrine version is 1.XX, the persistence was create by Visual Paradigm. The DQL is this:$q = Doctrine_Query::create()-&gt;select(&#8216;c.com_id&#8217;)-&gt;from(&#8216;fn_comuna c&#8217;)-&gt;innerJoin(&#8216;c.fn_provinci<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/39c5682c659c012d83008f2efb2fd331?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nJason Swett<br \/>\nsql symfony1 doctrine dql<br \/>\nI have the following SQL query:select bank.* from bank join branch on branch.bank_id = bank.id join account a on a.branch_id = branch.id join import i on a.import_id = i.idIt returns exactly what I expect.Now consider the following two DQL queries:$q = Doctrine_Query::create()-&gt;select(&#8216;Bank.*&#8217;)-&gt;from(&#8216;Bank&#8217;)-&gt;leftJoin(&#8216;Branch&#8217;)-&gt;leftJoin(&#8216;Account&#8217;)-&gt;leftJoin(&#8216;Import&#8217;);-$q = Doctrine_Query::create()-&gt;select(&#8216;Bank.*&#8217;)-&gt;from(&#8216;Bank&#8217;)-&gt;innerJoin(&#8216;Branch&#8217;)-&gt;innerJoin(&#8216;Accoun<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/7417975bab1cece8af8b5bf02523b2da?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nBee<br \/>\nsql database symfony-1.4 dql doctrine-1.2<br \/>\nI have following sql error:SQLSTATE[42S22]: Column not found: 1054 Unknown column &#8216;a.`role`&#8217; in &#8216;field list&#8217;My doctrine select is$query-&gt;select(&#8216;a.role AS role&#8217;);When i look on the symfony error i see that doctrine makes the &#8216;a.role&#8217; to a.role. Here the full SQL Statement = at Doctrine_Connection-&gt;execute(&#8216;SELECT `a`.&#8220;`role&#8220;` AS `a__0`, `a`.`role` AS `a__0` FROM `offer` `o` INNER JOIN `account` `a` *******)<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/ba6038cfddcfd1bbd76f995936930e46?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nuser1146284<br \/>\nphp mysql database doctrine dql<br \/>\nI&#8217;m trying to execute this DQL:$q = Doctrine_Query::create() -&gt;select(&#8216;*&#8217;); -&gt;from(&#8216;Clientes c&#8217;); $retorno = $q-&gt;execute();My BaseClientes.php:\/\/ Connection Component Binding Doctrine_Manager::getInstance()-&gt;bindComponent(&#8216;Clientes&#8217;, &#8216;padrao&#8217;);\/*** BaseClientes* * This class has been auto-generated by the Doctrine ORM Framework* * @property integer $pk_clientes* @property string $txt_nome* @property timestamp $ts_cadastro* @property string $txt_diretorio* @property Doctrine_Collectio<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/8ec250b462897c409c4576dfa30c52ce?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nRopstah<br \/>\ndoctrine dql<br \/>\nI&#8217;m trying to execute a query but I get an error:Unknown table aliasThe tables are setup as follows:Template_Spot hasOne Template Template hasMany Template_Spot Template hasMany Location Location hasOne TemplateI&#8217;m trying to execute the following DQL:$locationid = 1; $spots = Doctrine_Query::create()-&gt;select(&#8216;cts.*, ct.*, uc.*&#8217;)-&gt;from(&#8216;Template_Spot cts&#8217;)-&gt;innerJoin(&#8216;Template ct&#8217;)-&gt;innerJoin(&#8216;Location uc&#8217;)-&gt;where(&#8216;uc.locationid = ?&#8217;, $locationid)-&gt;execute();Doe<\/li>\n<\/ul>\n<p id=\"rop\"><small>Originally posted 2013-11-27 12:28:58. <\/small><\/p>","protected":false},"excerpt":{"rendered":"<p>rockerest php mysql doctrine2 dql 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,1],"tags":[],"class_list":["post-1683","post","type-post","status-publish","format-standard","hentry","category-semantic","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1683","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=1683"}],"version-history":[{"count":1,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1683\/revisions"}],"predecessor-version":[{"id":8806,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1683\/revisions\/8806"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=1683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=1683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=1683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}