0

I want to write a DQL query with Doctrine that allow me to find lowest integer available in a specific column.

Table(ABC)

A              B
----------------
0              M
1              P
2              T
4              X
5              L
7              C

I want a single scalar result. In this case, (SELECT LowestValueAvailable(A) FROM ABC) = 3.

I know how to write it in basic SQL, but I can't figure this out in DQL. This would be the exact SQL request I want to DQLize : Find the smallest unused number in SQL Server

Thank you.

Community
  • 1
  • 1
Ousret
  • 124
  • 8
  • `MIN` function? – rogeriolino Feb 24 '17 at 12:20
  • Won't work, because it would return 0. :) I need 3. – Ousret Feb 24 '17 at 12:21
  • 1
    Put your working SQL query in the post. – rogeriolino Feb 24 '17 at 12:23
  • See. for working SQL. http://stackoverflow.com/questions/684106/find-the-smallest-unused-number-in-sql-server – Ousret Feb 24 '17 at 12:27
  • Kinda hard to figure this out, isn't it? Doctrine allow us to write SQL, but it's unsafe. So if someone figure this out, It would be very appreciated :) – Ousret Feb 24 '17 at 12:44
  • Read [Doctrine Query Language](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html). – gp_sflover Feb 24 '17 at 12:52
  • In your model Column A data are integer or string values? – Hokusai Feb 24 '17 at 13:23
  • Question is not sense, lower value in your example is 0 so if you apply MIN() function that is the function you have to apply to get the min value then you will get as expected 0. Why you expect 3?. Maybe I'm living in another planet where 0 > 3 ???. Please clearify your question or prepare to receive down votes – Hokusai Feb 24 '17 at 13:31
  • I want the LOWEST integer NOT present in column. with minimum allowed = 0. – Ousret Feb 24 '17 at 14:06

1 Answers1

0

Assuming A as property that maps A column in database:

$dql = ' SELECT MIN(e.A) as minValue
         FROM YourBundle:YourEntity e
       ';
$result = $yourEntityManager->createQuery($dql)->getScalarResult();

With this query you get minValue in column A. If you need type var_dump($result)

In doctrine docs: http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#aggregate-functions

Hokusai
  • 2,219
  • 1
  • 21
  • 22
  • This isn't what I wanted, you responded too fast. "Find the smallest UNUSED number in DQL" with constraint, of course, cannot be negative or unsigned if you want. – Ousret Feb 24 '17 at 14:08
  • Ok, title of question is confusing for me. If you only want know the value of the number one approach could be get all distinct values using a dql query and process later the result. – Hokusai Feb 24 '17 at 15:35