I have this N1QL query on the server that I also want to run in my app:
SELECT specieskey, year, sum(count) count, sum(IFMISSINGORNULL(takenhome,0)) takenhome,assocquotakey,assocquotaname,assocquotacount,assocsizerange
FROM data
WHERE type="Catch"
AND assockey="1"
AND assocquotacount > 0
AND year=2020
GROUP BY year,specieskey,assocquotakey,assocquotaname,assocquotacount,assocsizerange
I have tried to convert it to SQLite:
var year = DateTime.Now.Year;
var _condition = Expression.Property(TYPE).EqualTo(Expression.String(typeof(Catch).Name));
_condition.And(Expression.Property(nameof(Catch.AssocKey).ToLower()).EqualTo(Expression.String(assocKey)));
_condition.And(Expression.Property(nameof(Catch.AssocQuotaCount).ToLower()).GreaterThan(Expression.Int(0)));
_condition.And(Expression.Property(nameof(Catch.Year).ToLower()).EqualTo(Expression.Int(year)));
var _groupBy = new IExpression[] {
Expression.Property(nameof(Catch.Year).ToLower()),
Expression.Property(nameof(Catch.SpeciesKey).ToLower()),
Expression.Property(nameof(Catch.AssocQuotaKey).ToLower()),
Expression.Property(nameof(Catch.AssocQuotaName).ToLower()),
Expression.Property(nameof(Catch.AssocQuotaCount).ToLower()),
Expression.Property(nameof(Catch.AssocSizeRange).ToLower())
};
using (var query = QueryBuilder.Select(
SelectResult.Property(nameof(Catch.SpeciesKey).ToLower()),
SelectResult.Property(nameof(Catch.Year).ToLower()),
SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.Count).ToLower())))
.As(nameof(Catch.Count).ToLower()),
SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.TakenHome).ToLower())))
.As(nameof(Catch.TakenHome).ToLower()),
SelectResult.Property(nameof(Catch.AssocQuotaKey).ToLower()),
SelectResult.Property(nameof(Catch.AssocQuotaName).ToLower()),
SelectResult.Property(nameof(Catch.AssocQuotaCount).ToLower()),
SelectResult.Property(nameof(Catch.AssocSizeRange).ToLower())
)
.From(dbSource)
.Where(_condition)
.GroupBy(_groupBy)
)
{
var result = query?.Execute()?.AllResults();
:
But when I run it I get the following error:
SQLite error (code 1): no such column: count in "SELECT fl_result(fl_value(_doc.body, 'specieskey')), fl_result(fl_value(_doc.body, 'year')), fl_result(sum(count)) AS "count", fl_result(sum(takenhome)) AS "takenhome", fl_result(fl_value(_doc.body, 'assocquotakey')), fl_result(fl_value(_doc.body, 'assocquotaname')), fl_result(fl_value(_doc.body, 'assocquotacount')), fl_result(fl_value(_doc.body, 'assocsizerange')) FROM kv_default AS _doc WHERE (fl_value(_doc.body, 'type') = 'Catch') AND (_doc.flags & 1 = 0) GROUP BY fl_value(_doc.body, 'year'), fl_value(_doc.body, 'specieskey'), fl_value(_doc.body, 'assocquotakey'),
2020-03-24 15:05:18.926772+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| ERROR) [Database] no such column: count (1/1)
2020-03-24 15:05:18.927196+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| WARNING) [Database] {DB#1}==> litecore::SQLiteDataFile /Users/jda/Library/Developer/CoreSimulator/Devices/AF4326D5-8F20-4103-BCDF-C0D5236F2640/data/Containers/Data/Application/EEEF9117-EC1C-405D-89BA-3AB8F09C4F9D/Library/Application Support/CouchbaseLite/data/angler.cblite2/db.sqlite3 @0x7f9f93e151b0
2020-03-24 15:05:18.927395+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| WARNING) [Database] {DB#1} SQLite error compiling statement "SELECT fl_result(fl_value(_doc.body, 'specieskey')), fl_result(fl_value(_doc.body, 'year')), fl_result(sum(count)) AS "count", fl_result(sum(takenhome)) AS "takenhome", fl_result(fl_value(_doc.body, 'assocquotakey')), fl_result(fl_value(_doc.body, 'assocquotaname')), fl_result(fl_value(_doc.body, 'assocquotacount')), fl_result(fl_value(_doc.body, 'assocsizerange')) FROM kv_default AS _doc WHERE (fl_value(_doc.body, 'type') = 'Catch') AND (_doc.flags & 1 = 0) GROUP BY fl_value(_doc.body, 'year'), fl_value(_doc.body, 'specieskey'), fl_value(_doc.body, 'assocquotakey'), fl_value(_doc.body, 'assocquotaname'), fl_value(_doc.body, 'assocquotacount'), fl_value(_doc.body, 'assocsizerange')": no such column: count
I thought I had “count” covered by using an alias for the sum function?
Further tests show that if I use this instead:
SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.Count).ToLower())))
.As("s1"),
SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.TakenHome).ToLower())))
.As("s2"),
and
Count = record.GetInt("s1"),
TakenHome = record.GetInt("s2"),
then it runs… However, it does not seem to group the data and returns a resultset that seems to be all records…???
Not sure whether this is a problem in the sum function or the GroupBy - or both? What should I do to get this working?
Thanks in advance!