Add an external count field to a query

BIZ TMS Aurelius
1

Hi,

here my need as I would have done with SQL:

SELECT
CUSTOMER.CODE,
CUSTOMER.NAME,
(
SELECT
COUNT(*)
FROM TABLEA
WHERE TABLEA.FIELD1 > 1
) AS ITEMCOUNT
FROM CUSTOMER
WHERE CUSTOMER.NAME LIKE 'A%'

I need to retrieve my class (customer) fields, plus a field that is a count of another table (ITEMCOUNT).

I'm trying to work with projections, but I can't find the way to obtain this.

Any help?

Thanks
2

You would have to use an SQL projection and inject that SQL code directly into your Aurelius criteria.

3

Hi,

from the documentation I can't undestand what to do,
can you give me a simple example.

Do you intend a code like this?

Results:= Manager.CreateCriteria<TCustomer>
.Select(TProjections.ProjectionList
.Add(TProjections.Prop('Code')
.Add(TProjections.Prop('Name')
.Add(TProjections.Sql<integer>('SELECT COUNT(*) FROM TABLEA WHERE TABLEA.FIELD1 >1').As_('ItemCount'))
)
.ListValues


4

Yes, that is it.

5

I'm trying to use:


Manager.CreateCriteria<TCustomer>
                                    .SetProjections(TProjections.ProjectionList
                                                                .Add(TProjections.Prop('Code'))
                                                                .Add(TProjections.Prop('Description'))
                                                                .Add(TProjections.Sql<integer>('SELECT ' +
                                                                                                'COUNT() ' +
                                                                                                'FROM TABLEA ' +
                                                                                                'WHERE TABLEA.CUSTOMERID = {id} ' 
).As_('ItemCount'))

But I receive this SQL error:
You have an error in you SQL....
"SELECT COUNT() FROM TABLEA WHERE TABLEA.CUSTOMERID = A.ID ...

Why "A.ID"??
I need to put the Id of Customer inside the SQL...

Thanks
6
To help undestand, here the full SQL version of what I need:

SELECT
CUSTOMER.CODE,
CUSTOMER.NAME,
(
SELECT
COUNT(*)
FROM TABLEA
WHERE TABLEA.CUSTOMERID = CUSTOMER.ID) AS ITEMCOUNT
FROM CUSTOMER
WHERE CUSTOMER.NAME LIKE 'A%'
ORDER BY ITEMCOUNT

Thanks
7

"A.ID" is because Aurelius prefixes tables with aliases "A", "B", "C", etc.. In your example SQL< instead of "Customer" it will add "Customer as A". There should be no problem with that, what is the full SQL generated and what is the error exactly?

8

How can I retrieve the full SQL generated?


The error exactly is 
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SELECT COUNT(*) FROM TABLEA WHERE TABLEA.CUSTOMERID = A.ID  As '' at line 1'


9

Please try to enclose your custom SQL inside parenthesis

10

Great!

With parenthesis works!

Thanks a lot