Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Counting rows
 

Pages: 1
LLBLGen Pro Runtime Framework
Counting rows
Page:1/1 

  Print all messages in this thread  
Poster Message
Alex Davidson
User



Location:
Bristol, UK
Joined on:
18-Sep-2006 17:51:08
Posted:
21 posts
# 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)
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14626 posts
# 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?


  Top
jmeckley
User



Location:
Harrisburg, PA, USA
Joined on:
05-Jul-2006 16:03:19
Posted:
403 posts
# Posted on: 05-Jan-2007 15:58:43.  
you could also use the int count = adapter.getdbcount(fields, filter, group, ...);
Jason Meckley
Programmer
 
Top
Alex Davidson
User



Location:
Bristol, UK
Joined on:
18-Sep-2006 17:51:08
Posted:
21 posts
# 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:

Code:

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?


  Top
jmeckley
User



Location:
Harrisburg, PA, USA
Joined on:
05-Jul-2006 16:03:19
Posted:
403 posts
# 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.
Code:
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
Jason Meckley
Programmer
 
Top
Chester
Support Team



Location:
Chicago, IL USA
Joined on:
15-Jul-2005 22:38:41
Posted:
223 posts
# 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.

  Top
Alex Davidson
User



Location:
Bristol, UK
Joined on:
18-Sep-2006 17:51:08
Posted:
21 posts
# 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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38044 posts
# Posted on: 08-Jan-2007 10:11:43.  
Alex Davidson 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 Regular Smiley 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.