Counting rows

Posts   
 
    
Posts: 21
Joined: 18-Sep-2006
# Posted on: 05-Jan-2007 12:11:59   

I have a query stored as an IPredicateExpression. I'm selecting a field from SecurityHistory, and I need to determine if the query returns any rows.

At present I'm trying to build a query that will simply count the rows:

(int)adapter.GetScalar(SecurityHistoryFields.SecurityPermission, ?, AggregateFunction.CountRow, predicate)

I'm not sure what to put for the second argument.

Also, all I really need from this is a boolean indicating whether or not any rows were returned from the SecurityHistory table. Is there a better way to do this?

(Using LLBLGen v2, with minor DataAccessAdapter alterations unrelated to this question)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 05-Jan-2007 15:28:49   

I'm a little bit confused about the query you want to execute. Would you please specify what you need as an SQL Query, and then I'll try to help you to memic it using the LLBLGen Pro code?

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 05-Jan-2007 15:58:43   

you could also use the int count = adapter.getdbcount(fields, filter, group, ...);

Posts: 21
Joined: 18-Sep-2006
# Posted on: 05-Jan-2007 18:04:04   

To be honest I'm not that hot at the SQL...

OK, the inheritance is something like this, and it's Target-Per-Entity:

User is a subclass of History UserSecurity is a subclass of SecurityHistory

Naming is t_user, t_history, t_user_security and t_security_history respectively on the database side, and fields are camelCased. Every table has an 'id' field which is the primary key.

UserSecurity.PUserId is a foreign key to User.Id.

Something like this is probably required:


SELECT TOP 1 t_security_history.securityPermission FROM t_user, t_security_history
    INNER JOIN t_user_security ON t_user_security.id = t_security_history.id
WHERE
(
        t_user.id = '00000000000000000000000000000001'
    AND t_security_history.securityPermission = @Permission
    AND t_user_security.pUserId = t_user.id
    AND t_security_history.isCurrent = @IsCurrent
)

The redundant use of t_user.id is a result of the modular way the security library builds the query; I figured it'd get optimised out at the database side. When the security library and its queries become more complex, such short cuts will be necessary to keep the code maintainable.

With my previous query attempts, LLBLGen was not generating the INNER JOIN. Do I have to explicitly specify the relations to use? Since these relations are part of the inheritance structure, shouldn't they be inserted automagically by the DQE? Or is this because IPredicateExpression does not determine joins, and I need to use RelationPredicateBucket for that?

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 05-Jan-2007 18:35:01   

predicates are simply clauses in the where statment, they do not have anything to do with the from statement. to construct a predicate on a related table requires the relationpredicatebucket.

Irelationpredicatebucket filter = new RelationPredicateBucket();
filter.predicateexpression.add(UserSecurityFields.Id == [your value here]);
filter.predicateexpression.add(SecurityHistoryFields.SecurityPermission == [your value here]);
filter.predicateexpression.add(SecurityHistoryFields.IsCurrent == true);
filter.predicateexpression.add(UserSecurityFields.PUserId == UserSecurityFields.Id);

filter.relations.addUserSecurity.Relations.SecuityHistoryonSecurityHistoryId);

int dbCount;
using(adatper)
{
    dbCount = adapter.GetDbCount(new EntityCollection(new UserSecurityEntityFactory.Create()), filter);
}

//you could simplify your fitler with the follow predicate expression
/*
filter.predicateexpression.add(UserSecurityFields.PUserId == [your value here]);
filter.predicateexpression.add(SecurityHistoryFields.SecurityPermission == [your value here]);
filter.predicateexpression.add(SecurityHistoryFields.IsCurrent == true);
*/

I noticed in your first post you want to return the number of rows. In the query in your second post you will only every return 1 row because of Top 1. you are not preforming any aggregate so you will return the string/date/number of securityPermisison.

if you want to return a single value of something use the GetScalar() function. If you want the number a rows a query will return execute the GetDBCount function. Less coding and easier to understand what is happening. pass in the colletion of fields, a filter, grouping if need be

Chester
Support Team
Posts: 223
Joined: 15-Jul-2005
# Posted on: 07-Jan-2007 19:31:51   

adapter.GetDbCount() would work fine to tell you how many rows/items exist in a table/collection.

Posts: 21
Joined: 18-Sep-2006
# Posted on: 08-Jan-2007 10:06:00   

All I actually need is a boolean indicating whether any rows are returned or not. I do not need the database to return all the rows, or count them all. It seems that it's more efficient to return TOP 1 and see whether or not something is returned, because then the database does not need to find all the matches.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 08-Jan-2007 10:11:43   

Alex wrote:

All I actually need is a boolean indicating whether any rows are returned or not. I do need the database to return all the rows, or count them all. It seems that it's more efficient to return TOP 1 and see whether or not something is returned, because then the database does not need to find all the matches.

Depends on how you look at it simple_smile To determine which row to return, it's easy if there's no sorter: just the first row you run into. If there's a sorter, the TOP1 has to sort first, which means ithas to read all rows.

I'd return the top1 on the PK without a sorter using a GetScalar.

Frans Bouma | Lead developer LLBLGen Pro