What would this look like in LLBLGen code?

SELECT ReferrerSearchPhrase, COUNT(ReferrerSearchPhrase) As Landed, SUM(CAST(Converted AS INT)) AS Converted, (CAST(SUM(CAST(Converted AS INT)) AS DECIMAL) / CAST(COUNT(ReferrerSearchPhrase) AS DECIMAL)) * 100 AS ConversionPercentage
FROM FormSession
GROUP BY ReferrerSearchPhrase

ReferrerSearchPhrase = NVARCHAR Converted = BIT

Output Row

SearchPhrase - "nice cheddar cheese" Landed - 18 Converted - 6 ConversionPercentage - 30.0

(etc)

You need a marketplace where I can pay cash dollars to people to convert SQL queries into LLBLGen predicate expressions while I'm still figuring it out, because it's easily the hardest part to get your head around.

Shortcut solution is to use a Database view. Otherwise you will have to implement your own IExpression-using DbFunctionCall class, since the CAST function is not supported at the moment, for more info please refer to the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6801 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8207

Here are some hints o how to build the query if you are going to follow the second route: 1- Use a dynamicList 2- Build a GroupByCollection to pass it to the Fetch method. 3- Heavy use of Expressions & DBFunctionCalls, use the ResultSetField.ExpressionToApply property. 4- Set the AggregateFunctionToApply property for the COUNT & SUM in the 2nd and 3rd fields.

The following is a sample of how you can build up the 4th field using nested expressions (I'm using the "ANY_DB_FUNCTION" as a general term for any DBFunction, you will have to use your own CAST implementation instead):

LEFT_EXPRESSION / ANY_DB_FUNCTION(COUNT(ReferrerSearchPhrase) AS DECIMAL)) * 100 AS ConversionPercentage

IExpression rightExp = new DbFunctionCall("ANY_DB_FUNCTION", new object[] { new DbFunctionCall("COUNT", new object[]{FromSessionFields.ReferrerSearchPhrase}) });

//IExpression leftExp = ...same work.

IExpression bigExp = new Expression(leftExp, ExOp.Div, rightExp);
IExpression outerExp = new Expression(bigExp, ExOp.Mul, 100);

myResultSetField.ExpressionToApply = outerExp;

Thanks for the reply Walaa.

The problem with a view is that I need to add a WHERE clause to filter the date range. As I'm on SQL Server I can pass parameters to a view

Otherwise a view would be a very easy get-out.

The solution using a dynamic list would be great - but the practical reality of it is incomprehensibly hard, especially for an LLBLGen n00b like me.

The third, imperfect solution is the one I'm going to have to take I think - a sproc - and suffer the lack of typed data access.

You win some, you lose some..

The problem with a view is that I need to add a WHERE clause to filter the date range. As I'm on SQL Server I can pass parameters to a view

Otherwise a view would be a very easy get-out.

You may add the Date fields to the select list, so the date fields can appear on the columns of the view, and then you can filter on those fields when quering the view. The view can be mapped to either a TypedView or an Entity, and in both cases LLBLGen Pro enables you to add a filter when you do the fetch.

Obviously a TypedView or Entity is a perfect solution.

But I have no idea how to add my "Created" DateTime column to the SQL statement - as it doesn't form part of the GROUP BY - so trying to add it to the SELECT list just causes an error about aggregate functions.

I'd love to be able to use say a TypedView and then add the predicate filter on the "Created" column, but I can't see any way of doing that - unless my SQL brain is broken today...

another cheat is to create the sql view and map the view as an entity (not view) in LLBL. make sure your fields are all marked readonly.

Would you please post the table structure with some possible values/rows? Also please post the complete SQL Query that you want to execute, including the filters.

Depending on the Values, sometimes you can use a dummy aggregate that won't affect the end result like MAX(DateField) or MAX(Id).

Table:

CREATE TABLE FormSession
(
    Id INT IDENTITY NOT NULL,
    ReferrerSearchPhrase NVARCHAR(256) NULL,
    Converted BIT NOT NULL DEFAULT 0,
    Created DATETIME NOT NULL DEFAULT GETDATE()
)

Example rows:

1, "best widget price", 0, "2007-10-13 11:20"
2, "best widget price", 1, "2007-10-14 12:20"
3, "best widget price", 0, "2007-10-14 13:20"
4, "best widget price", 0, "2007-10-14 14:20"
5, "best widget price", 0, "2007-10-15 15:20"
6, "best widget price", 0, "2007-10-15 11:20"
7, "lovely widget", 0, "2007-10-13 11:10"
8, "lovely widget", 0, "2007-10-14 16:40"
9, "lovely widget", 1, "2007-10-13 14:20"
10, "cheapest widget", 0, "2007-10-13 10:20"
11, "cheapest widget", 1, "2007-10-15 11:30"

Query:

SELECT ReferrerSearchPhrase, COUNT(ReferrerSearchPhrase) As Landed, SUM(CAST(Converted AS INT)) AS Converted, (CAST(SUM(CAST(Converted AS INT)) AS DECIMAL) / CAST(COUNT(ReferrerSearchPhrase) AS DECIMAL)) * 100 AS ConversionPercentage

FROM FormSession

WHERE Created > '2006-01-01' AND Created < '2008-01-01'

GROUP BY ReferrerSearchPhrase

Output:

ReferrerSearchPhrase | Landed | Converted | ConversionPercentage
---------------------------------------------------------------------------------------
best widget price       | 6        |   1              |   16.6666
lovely widget              | 3         |   1              |   33.3333
cheapest widget      | 2           |   1               |     50.0

Thanks Walaa!

Oh, and it also has optional ORDER BY clauses on the Landed, Converted and ConversionPercentage, depending on what the user wants to see in the report.

I have to admit that a Stored Procedure will be the easiest & best option in this case.

The third, imperfect solution is the one I'm going to have to take I think - a sproc - and suffer the lack of typed data access.

You can still project the resultSet to a structure of your own. ref: manual "Using the generated code -> SelfServicing/Adapter -> Fetching DataReaders and projections"

Damn, that sucks.

That means I'm back to all the old-school problems to do with stored procedures..

Like, I have to have parameters to take into account all the different WHERE and ORDER BY clauses - but in LLBLGen normally I would just dynamically add and remove them - the big benefit of Dynamic SQL over Sprocs.

you could also write the query this way

select   distinct f.ReferrerSearchPhrase,
            (
                   select  COUNT(f1.ReferrerSearchPhrase) 
                   from FromSession f1 
                   where f1.ReferrerSearchPhrase = f.ReferrerSearchPhrase
            ) as Landed,
            (
                   select  COUNT(f1.Converted) 
                   from FromSession f1 
                   where f1.ReferrerSearchPhrase = f.ReferrerSearchPhrase
                     and   (f1.Converted = 1)
            ) as Converted
from     FromSession
where  Created between @start and @end

I would then save Converted / Landed * 100 for the business logic. if you use a dynamic list it would look something like this

IEntityField2 Landed= new EntityField2("Landed", new ScalarQueryExpression(FromSessionFields.ReferrerSearchPhrase.SetAggregateFunction(AggregateFunction.Count), (FromSessionFields.ReferrerSearchPhrase == FromSessionFields.ReferrerSearchPhrase)));

IEntityField2 Converted = new EntityField2("Converted ", new ScalarQueryExpression(FromSessionFields.Converted .SetAggregateFunction(AggregateFunction.Count), (FromSessionFields.ReferrerSearchPhrase == FromSessionFields.ReferrerSearchPhrase) & (FromSessionFields.Converted == 1)));

IEntityField2 Precent = new EntityField2("Precentage", (Converted / Landed * 100));

ResultSet fields = new ResultSet(4);
fields.Define(FromSessionFields.ReferrerSearchPhrase, 0);
fields.Define(Landed, 1);
fields.Define(Converted, 2);
fields.Define(Precent , 3);

DateTime start = DateTime.Today;
DateTime stop = DateTime.Today.AddMonths(1);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new BetweenFieldPredicate(FromSessionFields.Created, start, stop);

DataTable resutls = new DataTable();
using(IDataAccessAdapter adapter = new DataAccesAdapter())
{
     //fields, data, filter, max rows, sort, distinct records.
     adpater.FetchTypedList(fields, results, bucket, 0, null, true);
}

if the precentage column doesn't work drop the 4th result set field and after fetched create an expression on the datatable like this.

ResultSet fields = new ResultSet(3);
fields.Define(FromSessionFields.ReferrerSearchPhrase, 0);
fields.Define(Landed, 1);
fields.Define(Converted, 2);
...
DataTable resutls = new DataTable();
using(IDataAccessAdapter adapter = new DataAccesAdapter())
{
     //fields, data, filter, max rows, sort, distinct records.
     adpater.FetchTypedList(fields, results, bucket, 0, null, true);
}
results.Columns.Add("Precent", typeof(float), "[Converted] / [Landed] * 100");

if nothing else this should get you real close

Wow. That's awesome.

I'll give it a go. It's crazy enough that it might just work!

Thank you.

OK, I ended up with the below as a quick start to test this would work. This is in VB, so the syntax is slightly different. Plus I had to use EntityField too as ResulsetFields does not accept an EntityField2 object.

        Dim Landed As New EntityField("Landed", New ScalarQueryExpression(FormSessionFields.ReferrerSearchPhrase.SetAggregateFunction(SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Count), FormSessionFields.ReferrerSearchPhrase = FormSessionFields.ReferrerSearchPhrase))

        Dim fields As New ResultsetFields(2)
        fields.DefineField(FormSessionFields.ReferrerSearchPhrase, 0)
        fields.DefineField(Landed, 1)

        Dim dynamicList As New System.Data.DataTable
        Dim dao As New Am.Leadbay.FormData.Data.DaoClasses.TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, False, Nothing, Nothing, 0, 0)

The problem is the output ends up as:

ReferrerSearchPhrase      |   Landed
-------------------------------------------------------
lovely widget                   |    10000
nice widget                    |     10000

(where 10000 is the number of entries in the database.. not the COUNT of the number of occurences of the ReferrerSearchPhrase)

Where am I going wrong with this sub-query?

you need to assign object aliasing to the sub query parameters. right now it's writing the query like this

select distinct
          ReferrerSearchPhrase,
          ( select count(ReferrerSearchPhrase) from FromSession where ReferrerSearchPhrase = ReferrerSearchPhrase) as landed
from   FromSession

you need this

select distinct
          a.ReferrerSearchPhrase,
          ( select count(b.ReferrerSearchPhrase) from FromSession b where a.ReferrerSearchPhrase = b.ReferrerSearchPhrase) as landed
from   FromSession a

Dim landedSelectField as EntityField = FormSessionFields.PKField;
landedSelectField.ObjectAlias = "landed";
landedSelectField.AggregateFunction = AggregateFunction.Count

Dim landedPredicateField as EntityField = FormSessionFields.ReferrerSearchPhrase;
landedSelectField.ObjectAlias = "landed";

Dim Landed As New EntityField("Landed", New ScalarQueryExpression(landedSelectField , landedPredicateField = FormSessionFields.ReferrerSearchPhrase))