0

I have a text field in SQL Server. I can't change the type.
And I need to do a group by...
My code :

 $queryBuilder= $this->createQueryBuilder('a');
 $queryBuilder ->select('a.MyfieldText');
 $queryBuilder ->groupBy('a.MyfieldText');

The result is:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]text, ntext, and image data types cannot be compared or ordered except when using the LIKE or IS NUL operator.

In SQL server manager studio this query it's ok :

SELECT cast(a.MyfieldText as varchar(250)) FROM TABLE GROUP BY cast(a.MyfieldText as varchar(250))

If i modify my query in symfony :

$queryBuilder ->select('cast(plt.pckliLInfo as varchar(255)));

I have this message :

Error: Expected known function, got 'cast'

What is good practice ?

Thom A
  • 88,727
  • 11
  • 45
  • 75
guillaume
  • 1
  • 2
  • Yes but I can't change the db – guillaume Oct 11 '22 at 12:26
  • Then you're stuck with needing to convert/cast the value; which could well be awful for performance. Though why are you converting to a `varchar(255)`? `text` can hold *up to* 2 **billion** characters and it's usage denotes the value is likely to have more than **8,000** characters. – Thom A Oct 11 '22 at 12:29
  • because SQL server does not allow group by on text , and i have max 180 characters – guillaume Oct 11 '22 at 12:36
  • Then *why* (again) are you using `text`? `text` has been deprecated since *2005* and was for when you have values that are likely to be 8,000 characters or more in length (prior to the additional of `MAX` length). If the value will be at most 180 characters there is **no reason** to be using that data type. Fix the design. – Thom A Oct 11 '22 at 12:40
  • Because I can not modify the structure of the database. There are related business applications – guillaume Oct 11 '22 at 12:44
  • Then get whomever can to fix it. *That* is your real solution. – Thom A Oct 11 '22 at 12:44
  • 1
    CAST is not included in DQL, you could either create it yourself or use raw sql https://stackoverflow.com/questions/3325012/execute-raw-sql-using-doctrine-2/69885184#69885184 – Dylan KAS Oct 11 '22 at 13:01
  • 3
    Seems like even more of a reason that that OP needs to fix the design then, @DylanKAS . The data type is deprecated, the data type *can't* be grouped on, the data type isn't appropriate (it's too large for the data they are storing) and they can't even `CAST` (or `CONVERT`?) it natively in the language they are using. Do we need any more nails? – Thom A Oct 11 '22 at 13:45

0 Answers0